Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
mbarnett

mbarnett

MIS
Jun 15, 2003
123
US
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
 
Sort by date Sort by votes
Is there any more in the specification?

What do you mean by "my criteria range is always changing." What range is that? What criteria are you using?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Hi,
Cusip should be the same in both sheets. There may be a diff between the other values. If the values do not match, then I want the mismatches along with the cusip to be spit out onto anther sheet.

Number of cusips is always changing. One day it could be 20 the next 200.
 
Upvote 0 Downvote
Use the OFFSET function (faq68-1331 How can I rename a table as it changes size) to Insert/Name/Define a range name for each column.

Here's a simple spreadsheet solution...
I Named the ranges on on sheet with the heading names (sans spaces) and the ranges on the second sheet with an "A" suffix
On first sheet...
Code: 
=SUMPRODUCT((CusipA=A2)*(NotionalA=B2)*(MatDateA=C2)*(CouponA=D2)*(Notional2A=E2))
On second sheet...
Code: 
=SUMPRODUCT((Cusip=A2)*(Notional=B2)*(MatDate=C2)*(Coupon=D2)*(Notional2=E2))
:)


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Hi mbarnett,

I have no doubt at all that Skip can, and most probably will, help you, but this is a public forum, not a personal help desk. Skip (amongst others) provides expert help to many people here, as and when he can but he has a life as well and it is not fair on him to direct questions specifically at him.

The best way to get help is simply to describe your problem clearly and anyone who can help, will. You won't get extra attention from Skip by asking for him by name, but you may get less attention from others.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Upvote 0 Downvote
Thanks. But I was looking for a code to loop through both spreadsheets and compare the values and those that did not match spit on seperate sheet. Renaming ranges will be troublesome as i mentioned it can be 5 one day then 2000 the next. I thought there was code that compared data.
 
Upvote 0 Downvote
My 2 sheets are Sheet2 & Sheet3 output to Sheet4....
Code: 
Sub ListMisses()
    With Sheet2.UsedRange
        For Each r In .Range(.Cells(.Row + 1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count))
            If r.Value = 0 Then
                With Sheet4
                    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 Sheet3.UsedRange
        For Each r In .Range(.Cells(.Row + 1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count))
            If r.Value = 0 Then
                With Sheet4
                    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


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Hi Skip,

Thanks for the code. Would I dim Sheet2 as variant. I keep getting an error
 
Upvote 0 Downvote
It is a Sheet Code Name -- change it to reflect your Sheet.CodeName. That's why I stated, "My 2 sheets are Sheet2 & Sheet3 output to Sheet4"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
hello All.

I have a similar issue and wanted to know if the code last posted would work.
I have two worksheets where I want to compare the price assigned to each cusip. Each worksheet varies in number of rows.
If the same cusip is found in each worksheet (duplicated), I want the macro to copy the cusip and price to a seperate worksheet.
I would rather create a loop rather than vlookup if possible.

I hope I was clear in my request.
Thanks for any help.
 
Upvote 0 Downvote
Mizzness,

Cut 'n' Paste 'n' try it!

mbarnett,

Sheets("utilities") ALSO has a Code Name Like Sheet1 or Sheet2 initially, unless you have changed it.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Skip,

I keep getting an error message at 1=.cells(1,1,).currentregion. It tells me variable not defined. The 1 has to be defined ???
 
Upvote 0 Downvote
Hi Skip,
i can 't seem to get the code to work. I Dimmed all the variables, but the code keeps crashing on .cells(l,i).value=r.offset(o,i-6).value
Thanks Again for you help. Should i replace UsedRange with an input range?


Sub ListMisses()
Dim r As Variant, l As Integer, i As Integer
With Sheets("Sheet1").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("sheet3").UsedRange
For Each r In .Range(.Cells(.Row + 1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count))
If r.Value = 0 Then
With Sheets("sheet1")
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
 
Upvote 0 Downvote
Skip,

Sorry about that. I have data in Sheet1 and Sheet2. I want to spit out the diffs in Sheet3. Column A in both sheets has the same id, but columns B,C,D,E may have different variables. I want to spit out the ids in which the variables do not match. Sorry I was not really clear.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
786
combo
combo
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
1K
strongm
strongm
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
691
combo
combo
arvarr
  • Locked
  • Question
Excel Help - Worksheets by UserID 3
2
Replies
39
Views
1K
arvarr
arvarr
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
628
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top