Compare multiple columns in Excel 1

[Andrzejek]

Let's say I have this data in Excel, 2 lists of similar data:
[pre]
A B C D
1 Name Age Name1 Age1
2 [blue]Andy 27 Andy 27[/blue]
3 [blue]Betty 21[/blue] Ann 24
4 Charlie 77 [blue]Betty 21[/blue]
5 Zak 30 Zak 43
[/pre]
and I would like to find which [blue]A/B data matches C/D data[/blue], no matter which rows the data is in.

Would be even better if I can [blue]align[/blue] the matching data:
[pre]
A B C D
1 Name Age Name1 Age1
2 [blue]Andy 27 Andy 27[/blue]
3 Ann 24
4 [blue]Betty 21 Betty 21[/blue]
5 Charlie 77
6 Zak 30
7 Zak 43
[/pre]
Now that I look at it, I may need to venture into a VBA solution....

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Scott24x7]

Andy,

Apply this assuming you are starting at Row 2 after your header in column E, and then copy it down along side all rows of column E:

=IF(AND(A2<>"", B2<>""), IF(COUNTIFS(C:C, A2, D, B2) > 0, "Match", ""), "")

Now, you have a column (E) that indicates "Match" for the rows where A/B data matches C/D data.
To align the matching data, you can use conditional formatting. Select the cells in columns A, B, C, and D where you have data.
Go to the "Home" tab on the Excel ribbon.
Click on "Conditional Formatting" in the "Styles" group.
Choose "New Rule."
In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
In the "Format values where this formula is true" field, enter the following formula to format cells based on the "Match" status in column E:

=$E2="Match"

Click on the "Format" button to set the formatting for the matching cells. You can change the font color, background color, or any other formatting as per your preference.
Click "OK" in the "Format Cells" dialog box.
Click "OK" again in the "New Formatting Rule" dialog box.
Now, the matching data in columns A, B, C, and D will be aligned and formatted as per your conditional formatting settings. Rows that don't have matching data will remain as they are.

You can play around with this a bit to get some different results.
I would suggest that using "helper columns" as I've suggested here with Column E can enable a ton of stuff in Excel that most only think can be done with VBA. I can tell you, I've been developing in Excel for over 20 years, and to this day I have only 1 (very complex) workbook that uses a VBA macro because it just can't be done in sheet. But, MS continues to enhance Excel's capability, and I have been able to remove the need for VBA as a result of these new advancements. (If you haven't tried data slicers yet, that is crazy powerful).

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Andrzejek]

WOW! Scott, these are very detailed instructions.
Much appreciated.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Andy,

Aren't you actually comparing 2 tables with identical fields, like...

After doing the compare in each table, I sorted Table2 by Match Row descending. Now you have a clear shot at the unmatched.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

Power query single command allows to link two tables, on two fields, with outer join. Output by default is sorted differently however, needs additional processing if it is critical. In attached workbook: two queries to get tables to PQ environment only, one query to join tables and output to worksheet.

combo