[Webinar] Streamline your web hosting managementRegister Today

x
Solved

Compare Two Lists in Excel

Posted on 2014-12-15
Medium Priority
125 Views
Hello,

i have seen many cell by cell comparisons in excel, also column by column comparison.

however i want is comparison of by entire row values.

for example, see attached file.  i have two sheets "List 1" and "List 2"  in sheet List 1 i want to put a text in column N for those rows that thier match found in List 2 and similalrly, i want to put text in Column N of List2 for those rows that thier match found in List1.

thanks.
Book1.xlsx
0
Question by:Flora

LVL 24

Assisted Solution

Phillip Burton earned 1000 total points
ID: 40500062

I've also added some conditional formatting to reflect the colors you used.
EE141215.xlsx
0

LVL 54

Accepted Solution

Rgonzo1971 earned 1000 total points
ID: 40500065
Hi,

you could these 2 formulas as Array formula Ctrl-Shift Enter

=IF(IFERROR(MATCH(A2&B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2,List2!\$A\$2:\$A\$25&List2!\$B\$2:\$B\$25&List2!\$C\$2:\$C\$25&List2!\$D\$2:\$D\$25&List2!\$E\$2:\$E\$25&List2!\$F\$2:\$F\$25&List2!\$G\$2:\$G\$25&List2!\$H\$2:\$H\$25&List2!\$I\$2:\$I\$25&List2!\$J\$2:\$J\$25&List2!\$K\$2:\$K\$25&List2!\$L\$2:\$L\$25&List2!\$M\$2:\$M\$25,0),0),"Found in List 2","")

=IF(IFERROR(MATCH(A2&B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2,List1!\$A\$2:\$A\$25&List1!\$B\$2:\$B\$25&List1!\$C\$2:\$C\$25&List1!\$D\$2:\$D\$25&List1!\$E\$2:\$E\$25&List1!\$F\$2:\$F\$25&List1!\$G\$2:\$G\$25&List1!\$H\$2:\$H\$25&List1!\$I\$2:\$I\$25&List1!\$J\$2:\$J\$25&List1!\$K\$2:\$K\$25&List1!\$L\$2:\$L\$25&List1!\$M\$2:\$M\$25,0),0),"Found in List 1","")

See example

Regards
EE20141215.xlsx
0

LVL 6

Author Closing Comment

ID: 40500075
Thank you very much guys.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…