[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Compare Two Lists in Excel

Posted on 2014-12-15
3
Medium Priority
?
125 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 1000 total points
ID: 40500062
Please find attached.

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

Accepted Solution

by:
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

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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

607 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