Finding matching info across multiple columns & worksheets

Hi!

I apologize if this has been answered, but I've searched for hours and cannot find anything that quite comes close to what I need to do. I need to accomplish the following:

I have tons and tons and tons of data that I need to compare to even MORE data. I need to identify information from multiple columns in 2 worksheets.

For example:

Column A= First name, B= Last name, C= Job title, D= Company name

This applies to BOTH worksheets. What I need to do is figure out what rows ALL those fields are the same on both sheets. Basically, I just need to identify duplicates from the same row (A1:D1 for example) on sheet 1, and then somehow indicate if that information is the same on ANY row in sheet 2. Ideally, I'd like something like a true/false or a 1 & 2 to appear, so I can then filter the results to keep ONLY the rows that show up with the same information on both sheets. I don't really care how that's indicated, I just need a way to eventually sort the results.


Here's an example of data:

Sheet 1:
First Last Title Company
Sally Smith CEO ABC
Bob Johnson VP DEF
Rick Cameron CIO GHI
Betty White CEO JKL
Sam Peters VP ABC


Sheet 2:
First Last Title Company
Mark Jones CMO DEF
Sally Smith CEO ABC
Bob Blue VP DEF
Michael Cameron CIO GHI
Betty White CEO JKL
Sam Peppers CIO ABC

Now, IDEALLY, I'd like a formula that, in another column on Sheet 1, identifies the rows of data that are an exact match of rows on sheet 2. I have like, 100k rows of data on my real sheet 1, and about 20k of data on my real sheet 2, so this is a BIG project. I can't just manually go through and see if sheet 2 has a Betty White who is a CEO at JKL company, and then mark it on sheet 1 if there's a match.

I've tried playing around with IF, AND, MATCH, VLOOKUP, LOOKUP, and probably just about all the other possible formulas, but I cannot figure it out!

I hope this makes sense, I can try to answer any questions if it's not.

Thanks!!!

This is easy with a couple added helper columns. Even with a lot of data this should be robust.

1) Create a concatenation column, a column that grabs all the values and makes one long key string you can evaluate a single thing.

1. Sheet1 - In F1, put the word "Key"
2. In F2, put this formula: =A2&"-"&B2&"-"&C2&"-"&D2
3. Copy F2 all the way down the dataset to get your KEY strings
4. Repeat this process on Sheet2
5. Assuming you want the TRUE/FALSE matches listed on sheet2, in G2, put this formula: =ISNUMBER(MATCH(F2, Sheet1!F:F, 0))
6. Copy G2 down the whole dataset and your TRUE/FALSE answers will appear.
7. Filter Sheet2 by column G

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.