Compare 2 lists in Excel ?

excel list-comparison
Mark picture Mark · Feb 8, 2013 · Viewed 16.4k times · Source

I have 2 columns in a spreadsheet. One column has around 26 extra rows than the other. I've been trying various formulas to highlight or somehow indicate which columns are missing from the smaller of the lists...

I tried filling a 3rd colum with this : 

=FIND(B1,A1:A1102)

which I though returned 1 if b1 was in the list a1:a1102 alas it doesn't seem to be true.

Anybody got any solutions for comparing 2 lists and isolating differences?

Thanks

Answer

chuff picture chuff · Feb 8, 2013

To use MATCH, go with something like the following:

 =IFERROR(MATCH(B1,$A$1:$A$1102,0),0)

entered into cell C1 and copied down to the end of the data in column B

This assumes that column B contains the longer list and A the shorter, of course.

The MATCH formula will return the row in which B1 is matched in A.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More