Compare data in multiple columns across 2 worksheets

[INDENT]I hope you're ready for this one because I sure am not! I have two worksheets with identical headers. Worksheet 1 has multiple entries. Say for example:

WORKSHEET 1
...A............B...............C...............D
88588......5/4/10......Brown, Jim......0.00
93818......5/4/10......Brown, Jim......5.40
00529......5/8/10......Brown, Jim......0.00
88588......5/4/10......Berry, Carl......0.00

*Notice the last entry for Jim Brown has a different date.

Worksheet 2 might only have one entry for Jim Brown, and I want to extract the value in Column D of worksheet 2, to be placed in column D of Worksheet 1, here is the kicker, I want to preserve the unmatched values of Worksheet 1

So Worksheet 2 might look like this:

...A............B...............C...............D
88588......5/4/10......Brown, Jim......97.00
92911......6/2/10......Alfred, Kim......50.29

I want to write a formula that will look at C2 in Worksheet 2, find a match in Worksheet 1, if a match is found, compare B2 in Worksheet 2 to the matched row (B2 in Worksheet 1?), if a match is found, compare A2 in Worksheet 2 to the matched row (A2 in Worksheet 1?) if its all true, output the value in D2, Worksheet 2, in this case "97.00." If any one of those conditions prove untrue, I don't want anything output.

This would be much easier if the two data sets matched up perfectly, but as you can see, even if I filter and sort by name, there is only 1 entry for Jim Brown in Worksheet 2 but 3 entries in Worksheet 1. I know its all a bit confusing but I am at wits end after obsessing all day on finding the right mix of Index/Match, If/ISNA/VLookup or some mixture thereof, I'm not that good at Excel and I NEED HELP!

Here is another problem if you're up to it. I don't want to put the formula in D2 of Worksheet 1 because I want to preserve values for which there was no match. In the example I provided above, if I place the formula in E2, it will show:

...A............B...............C...............D...........E...
88588......5/4/10......Brown, Jim......0.00......97.00
93818......5/4/10......Brown, Jim......5.40
00529......5/8/10......Brown, Jim......0.00
88588......5/4/10......Berry, Carl......0.00

This tells me I need to change D2 to = E2, so I could copy it over. But with 120,000+ rows, that could get extremely cumbersome. How do I, en masse, update Column D with (if) values are found in Column E, but preserve values in Column D that do not need updating (so 5.40 will still be there)? [/INDENT]

Re: Compare data in multiple columns across 2 worksheets

Tell me if you think I'm on the right track... here is the formula I've come up with:

=INDEX(TBP!$Z$2:$Z$2484,MATCH(U2&C2&E2,TBP!$U$2:$U$2484&TBP!$C$2:$C$2484&TBP!$E$2:$E$2484,0))

What I'm trying to tell Excel to do is RETURN me the values from Worksheet TBP range Z2:Z2484, but first, find a match for cells U2, C2, and E2 of THIS worksheet within Worksheet TBP (associated ranges), and return an exact match "0."

When I tried to execute this formula I got the #VALUE! error. What was interesting is that when I highlight the formula in the formula bar, anything related to TBP! did not get colored, as if Excel didn't recognize those cells or what I was asking it to do.

Re: Compare data in multiple columns across 2 worksheets

And yes, I did CSE (control shift enter) the above formula

Re: Compare data in multiple columns across 2 worksheets

Okay, so I used the utility that evaluates the formula step by step and here is what Excel sees and says "The next evaluation will result in an error." I'm assuming the error its talking about is that #VALUE! message I get... anyway, here is what Excel sees:

=INDEX(TBP!$Z$2:$Z$2484,MATCH"Smith, John4022199215","Doe, Jane4022199215",0))


For those of you who don't know what all of this is, it evaluated U2, C2, and E2...

U2 is the name "Smith, John"

C2 is a date that Excel changed to number format "40221"

and E2 is a code "99215"

Excel did correctly evaluate each one independently, then put them together (I'm assuming) so it can find matches mor easily.

Excel then correctly evaluated TBP!$U$2:$U$2484&TBP!$C$2:$C$2484&TBP!$E$2:$E$2484 and came up with the product:
"Doe, Jane4022199215"

Now its comparing these two values to see if they match, and they don't, which is why Excel is giving me the #VALUE! error (I'm assuming). But my question now is: Why isn't Excel checking the rest of TBP!$U$2:$U$2484&TBP!$C$2:$C$2484&TBP!$E$2:$E$2484? What I mean is "Doe, Jane4022199215" is only what is found in the 2nd row, and I could've sworn by using absolute ranges I'm instructing Excel to look through the entire column until it finds a match. What gives? What am I doing wrong?

Re: Compare data in multiple columns across 2 worksheets

I know this is starting to look like a self help journal but here is another update:

Before I was writing the formula in notepad and copy and pasting this formula into my worksheet:

=INDEX(TBP!$Z$2:$Z$2484,MATCH(U2&C2&E2,TBP!$U$2:$U$2484&$C$2:$C$2484&$E$2:$E$2484,0))

Then going back and pressing Ctrl+Shift+Enter... I now know that is improper procedure. You must hand type it to CSE a formula, who'da thunk it?!

Anywho, now that I'm doing that, I'm getting #N/A as an error... stay tuned for an update or, PLEASE HELP so I can get done with this, deadline is Friday and I really don't want to go through 120,000 lines 36 columns across trying to do this update.

Re: Compare data in multiple columns across 2 worksheets

Okay, so I used that formula evaluating utility again and its just #N/A everything, it doesn't appear to be evaluating a single instruction. Maybe it was better to not CSE it.

Re: Compare data in multiple columns across 2 worksheets

I just got done trying this variation:

=INDEX(TBP!$Z$2:$Z$2484,MATCH(U2,TBP!$U$2:$U$2484,0),MATCH(C2,TBP!$C$2:$C$2484,0),MATCH(E2,TBP!$E$2:$E$2484,0))

to no avail, still getting #N/A error. If its not apparent what I've done here, instead of grouping all three criteria together in a single MATCH statement I have them separated into individual commands.

Re: Compare data in multiple columns across 2 worksheets

JohnnyBeck,

I posted that last post (the variation of earlier formulas) before I saw your response. THANK YOU SO MUCH! I just don't know the first thing about Macros, which is why I haven't resorted to using one yet. I'd love to try your implementation, how do I get started?

Re: Compare data in multiple columns across 2 worksheets

Quote from JohnnyBeck;560243

Right click over your sheet1 tab and select "view code". That will open your VBA window. Then go to the top left area of your screen and right click over the little folder that says, "Microsoft Excel Objects". Insert - module.

Paste the code I gave you into the new blank module screen. Then click "run" in the toolbar.

This macro assumes that your sheets are named Sheet1 and Sheet2. If they aren't, you can adjust the code to match your actual sheet names.

[INDENT]JohnnyBeck,

I posted that last post (the variation of earlier formulas) before I saw your response. THANK YOU SO MUCH! I just don't know the first thing about Macros, which is why I haven't resorted to using one yet. I'd love to try your implementation, how do I get started? [/INDENT]

Re: Compare data in multiple columns across 2 worksheets

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).

Re: Compare data in multiple columns across 2 worksheets

sorry, I think we're responding to each other a little faster than the site can keep up b/c I could've sworn I copied your earlier post.

Re: Compare data in multiple columns across 2 worksheets

Quote from JohnnyBeck;560249

Make sure both sheet names are named correctly in the code. Sheet 1 and Sheet 2 need to be named in the code.

Yup, Sheet 1 is actually named Sheet 1, Sheet 2 is named TBP, here is how the code looks after I changed it:

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("Sheet1").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

I don't mean to ask too much, but could you help me understand what all this coding is doing?

Re: Compare data in multiple columns across 2 worksheets

Thanks, that did help! I think I may also know what the cause of the error is (I'm still getting Run-time error 9, subscript out of range). The columns that I'm looking for are U, C, and E.

I want to check U, C, and E in Sheet 1. Find their match it sheet: TBP (also columns U, C, and E). If a match is found I want to extract the value in column Z of TBP and put it in Column AN of Sheet 1.

So maybe the error is that the values I need looked at are not in adjacent columns? idk