Solved

Compare Two Lists in Excel

Posted on 2014-12-15
3
113 Views
Last Modified: 2014-12-15
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
Comment
Question by:Flora
3 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40500062
Please find attached.

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

Accepted Solution

by:
Rgonzo1971 earned 250 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

by:Flora
ID: 40500075
Thank you very much guys.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question