Compare data in multiple columns across 2 worksheets

Re: Compare data in multiple columns across 2 worksheets

Quote from cytop;560259

Lawsonrw - please edit post #14 to include code tags. These are required by the rules here. If you need further information, please have a read on this link.
In post #14 I wrote:

It would be appreciated if you comply. You were already reminded of the rules with regard to thread titles yesterday and subsequently double posted (also against the rules).

In Post #14 I wrote
[INDENT]Followed your instructions, hit run, got "Run-time error '9': Subscript out of range" I even changed the "Sheet2" to TBP (the name of my second worksheet). [/INDENT]

If you by code tags you mean the reference to sheet 2 I'm guessing that would be:

Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole) '''Sets a new range for each time the value from Sheet1 shows up in TBP sheet's Column C
For Each found In Sheets("TBP").Range(nameCol)

Otherwise the actual error message is a pop-up notification from Mircrosoft Visual Basic and I don't know what associated tags could accompany that string. As you can see since our last correspondence I have since tried to remain compliant, but on this matter I do not what exactly you are referring to.

Re: Compare data in multiple columns across 2 worksheets

When I hit debug on the prompt it highlights the line that reads:

For Each cell In Sheets("Sheet1").Range(nameCol)

Re: Compare data in multiple columns across 2 worksheets

Okay, so I changed the code to

Sub multmatch()
Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$C$2:$C$" & totalrows
 
For Each cell In Sheets("Sheet 1").Range(nameCol)
Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)
For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, -1) & found.Offset(0, -2) Like cell & cell.Offset(0, -1) & cell.Offset(0, -2) Then
cell.Offset(0, 1).Value = found.Offset(0, 1).Value
End If
Next found
Next cell
End Sub

The change I needed to make was to put a space between "Sheet1" to make it "Sheet 1" Sorry for the confusion. That said, it appears the script will execute THANKS A TON! But where is the output? I hit run, it did its thing for a while, but I don't see anything different. Do I need to have a certain cell highlighted or is there some other step I'm missing?

Re: Compare data in multiple columns across 2 worksheets

Thanks Kris, I tried the code as:

=LOOKUP(2,1/((TBP!$A$2:$A$10=A2)*(TBP!$B$2:$B$10=B2)*(TBP!$C$2:$C$10=C2)),TBD!$D$2:$D$10)

When I press enter it brings up the popup that opens new files except it says in the header "Updated Values: TBD" Is that intended, and if so, am I creating a new file here?

Re: Compare data in multiple columns across 2 worksheets

Quote from Krishnakumar;560268

Hi lawsonrw,

What cytop said was wrapping the VBA code within the code tags. This time I added for you.

try

=LOOKUP(2,1/((Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2)*(Sheet2!$C$2:$C$10=C2)),Sheet2!$D$2:$D$10)

HTH

Display More

Okay, second attempt. Updated formula to:

=LOOKUP(2,1/((TBP!$U$2:$U$100000=U2)*(TBP!$C$2:$C$10000=C2)*(TBP!$E$2:$E$10000=C2)),TBD!$Z$2:$Z$2484)

But its still giving me that window... help please?

Re: Compare data in multiple columns across 2 worksheets

Quote from Krishnakumar;560273

Hi,

All the range should be same size. 100000,10000,2484 this won't work. make everything uniform.

Alright, I tried it as:
=LOOKUP(2,1/((TBP!$U$2:$U$2484=U2)*(TBP!$C$2:$C$2484=C2)*(TBP!$E$2:$E$2484=C2)),TBP!$Z$2:$Z$2484)

and
=LOOKUP(2,1/((TBP!$U$2:$U$10000=U2)*(TBP!$C$2:$C$10000=C2)*(TBP!$E$2:$E$10000=C2)),TBP!$Z$2:$Z$10000)

across 1000 lines of data and both versions gave me #N/A... at least its not doing that thing with the open a new file window

Re: Compare data in multiple columns across 2 worksheets

Thanks for sticking with me Johnny, here is what I ran:

Sub multmatch()
Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$C$2:$C$" & totalrows
 
For Each cell In Sheets("Sheet 1").Range(nameCol)
Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)
For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, 2) & found.Offset(0, 18) Like cell & cell.Offset(0, 2) & cell.Offset(0, 18) Then
cell.Offset(0, 37).Value = found.Offset(0, 23).Value
End If
Next found
Next cell
End Sub

It did execute, but I don't know where to look for the output. I don't see anything standing out as different on either sheet. And, if by constant you mean what all the other data fields hinge on, then yes. But values in column C can change, there might be 2 or 3 lines of "Doe, John" followed by a line of "Smith, Richard" followed by two more lines of "Jane, G.I."... not sure if this is important or not, just wanted to let you know Column C does have different values within it.

Re: Compare data in multiple columns across 2 worksheets

Quote from Krishnakumar;560277

Hi,

Check the highlighted criteria variable and correct it

=LOOKUP(2,1/((TBP!$U$2:$U$10000=U2)*(TBP!$C$2:$C$10000=C2)*(TBP!$E$2:$E$10000=C2)),TBP!$Z$2:$Z$10000)

Alright, updated to formula to:

=LOOKUP(2,1/((TBP!$U$2:$U$2484=U2)*(TBP!$C$2:$C$2484=C2)*(TBP!$E$2:$E$2484=E2)),TBP!$Z$2:$Z$2484)

Still giving me #N/A

Re: Compare data in multiple columns across 2 worksheets

Quote from JohnnyBeck;560282

Yea that's what I meant. You should see a change in column NA of your Sheet 1 since that's where you wanted to put the data from column Z of your other sheet. Only if all 3 criteria match though (Name, colE, colU)

Checked 1000 lines of column NA and there has been no change... also, if I wanted to make the output appear in column AN, how do I modify the code to do that?

Re: Compare data in multiple columns across 2 worksheets

Quote from Krishnakumar;560283

Can you attach the workbook ?

check if there is any leading or trailing space in either criteria or lookup range.

Yup, give me a few minutes to scrub the data. I will post a sample set of 50 lines... do you think that will be sufficient or do you want more?