Comparing two worksheets...Skip Help!!!!! 1

[mbarnett]

Hi All,

I want to compare two string values in two different worksheets. Each string value has four other values I want to compare based on the string value. If I do not match on any of values of the string then I want the results (mismatching strings and the values) to be printed out on another sheet. I don’t want to use vlookup or sumif formulas because my criteria range is always changing. Here is an example of the data this is supposed to be the same on both worksheets:


String
Cusip Notional Mat Date Coupon Notional 2
999hf2 50000 2/1504 5.00 100000
999hf3 50000 2/1504 5.00 100000
999hf4 50000 2/1504 5.00 100000
999hf5 50000 2/1504 5.00 100000
999hf6 50000 2/1504 5.00 100000
999hf7 50000 2/1504 5.00 100000
999hf8 50000 2/1504 5.00 100000

 

[Mizzness]

Skip or anyone following this thread,

I used your code (see below) but did not get the results I wanted.
What I am trying to do is take values in one column based on the ID assigned to it in another column and compare this to the same data in another worksheet.
Any differences will be sent to a third worksheet listing the values and the ID assigned to those values in their respective columns.

Sub ListMisses()
Dim r As Variant, l As Integer, i As Integer
With Sheets("PFCA Raw").UsedRange
For Each r In .Range(.Cells(.Row + 1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count))
If r.Value = 0 Then
With Sheets("Sheet2")
l = .Cells(1, 1).CurrentRegion.Rows.Count + 1
For i = 1 To 5
.Cells(l, i).Value = r.Offset(0, i - 6).Value
Next
End With
End If
Next
End With
With Sheets("FIRC Raw").UsedRange
For Each r In .Range(.Cells(.Row + 1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count))
If r.Value = 0 Then
With Sheets("sheet2")
l = .Cells(1, 1).CurrentRegion.Rows.Count + 1
For i = 1 To 5
.Cells(l, i).Value = r.Offset(0, i - 6).Value
Next
End With
End If
Next
End With
End Sub


I hope I was clear and any help is appreciative.

Thanks
Mizzness

 

[SkipVought]

It works...

as long as...

you are using the SUMPRODUCT formulas posted above that yield 0 or 1 on each row.

I recreated the test bed and formulas and IT WORKS!



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW, I have these formulas in column F

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mbarnett]

Hi Skip,

I'm using hr below macro, instead of showing me what column my values don't match I want it to tell me what Id is not matching -

Sub CompareSheets()
Dim shtA As Worksheet
Dim shtB As Worksheet
Dim shtE As Worksheet
Dim rng As Range
Dim a As Range
Dim b As Range
Dim nErrorOutRow As Long
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nFirstCol As Integer
Dim nLastCol As Integer

' Update the following with the actual sheet names:
Set shtA = Worksheets("Sheet1")
Set shtB = Worksheets("Sheet2")
Set shtE = Worksheets("Sheet3")

' Determine rows and cols to test
With shtA.UsedRange
nFirstRow = .Cells(1, 1).Row
nLastRow = .Rows.Count + .Cells(1, 1).Row - 1
nFirstCol = .Cells(1, 1).Column
nLastCol = .Columns.Count + .Cells(1, 1).Column - 1
End With
With shtB.UsedRange
nFirstRow = WorksheetFunction.Min(nFirstRow, .Cells(1, 1).Row)
nLastRow = WorksheetFunction.Max(nLastRow, .Rows.Count + .Cells(1, 1).Row - 1)
nFirstCol = WorksheetFunction.Min(nFirstCol, .Cells(1, 1).Column)
nLastCol = WorksheetFunction.Max(nLastCol, .Columns.Count + .Cells(1, 1).Column - 1)
End With
Set rng = Range(Cells(nFirstRow, nFirstCol), Cells(nLastRow, nLastCol))

' Set up exceptions sheet
shtE.Cells.Clear
shtE.Cells(1, 1) = "Ref."
shtE.Cells(1, 2) = shtA.Name
shtE.Cells(1, 3) = shtB.Name
nErrorOutRow = 2

' Process all cells and compare
Application.ScreenUpdating = False
For Each a In shtA.Range(rng.Address)
Set b = shtB.Range(a.Address)
If a.Value <> b.Value Then
shtE.Cells(nErrorOutRow, 1) = b.Address(0, 0)
'shtE.Cells(nErrorOutRow, 1) = Cells(i, 1).Value
shtE.Cells(nErrorOutRow, 2) = a.Value
shtE.Cells(nErrorOutRow, 3) = b.Value
nErrorOutRow = nErrorOutRow + 1
End If
Next a
Application.ScreenUpdating = True
shtE.Activate

' Clean up and terminate
Set shtA = Nothing
Set shtB = Nothing
Set shtE = Nothing
Set rng = Nothing
Set a = Nothing
Set b = Nothing
End Sub

 

[SkipVought]

If this is another problem, mebe you ought to start another thread.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mbarnett]

Same issue. Different code

 

[SkipVought]

Well your code does not accomodate an EXACT MATCH on a different row.

You answer, though, is

with a
  shtE.Cells(nErrorOutRow, 4) = a.Offset(0, -1 * a.Column + 1).Value
end with

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mbarnett]

Thank You Skip for all your help and patience. Have a great day. I want to make a couple more changes and if I have any new problems I will start a new thread. Thanks
Again




Mike