Find and Compare Two Columns Excel (With Screenshots)

Question

I have a spreadsheet that will occasionally get new data that I don't know the contents of, I just have to add it to the spreadsheet. Some of the new data is just updating rows that are already in the spreadsheet, and other data is adding new rows. I'm looking for a way to add a column that will tell me if something has changed in the row when I compare the old spreadsheet to the new one.

The sheets have one column that will always have a unique value among all the rows, so I can use that to match rows if the sheets aren't sorted the same way. Here are some screenshots to show what I'm trying to do:

Old Spreadsheet:

New Spreadsheet:

The only solution I can think of is a large nested IF formula that compares each column one by one, something like:

=IF(Old!B2=New!B2,IF(Old!C2=New!C2,"NO","YES"),"YES")

The problem with that is that it gets very hard to look at since my actual data is using 33 columns (not including this "Changed?" column) and new columns could be added in the future.

I'm not very technical with Excel, nor have I ever used VBA, so I apologize in advance if there is a simple/obvious solution that I'm missing.

Thanks in advance for your help.

Answer 1

In this case, specialized software for Excel compare is better.

My company use this software. Check it out.

http://www.suntrap-systems.com/ExcelDiff/

http://www.youtube.com/watch?v=QQgnWr_RT-8

Answer 2

Using your example, in the 'New' sheet cell D2 and copied down:

=IF(COUNTIF(Old!A:A,A2)=0,"YES",IF(SUMPRODUCT(COUNTIF(INDEX(Old!A:AG,MATCH(A2,Old!A:A,0),0),LEFT(A2:AG2,254)&"*"))=SUMPRODUCT(COUNTIF(A2:AG2,LEFT(A2:AG2,254)&"*")),"NO","YES"))

Answer 3

vlookup would also work well for this problem.

in D2, the formula would be:

=IF(AND(VLOOKUP(A2,Old!A:C,2,FALSE)=B2,VLOOKUP(A2,Old!A:C,3,FALSE)=C2),"NO","YES")

The column numbers (2 and 3) are the columns that correspond to the data you are trying to match, using the ID column.

It's possible to find the appropriate column using MATCH if the column names you have match the column names in the old sheet

This would make the formula look more complex, but Excel would adjust the Old!A:C reference if more columns are inserted.

The formula would look like this to match against column names

=IF(AND(VLOOKUP(A2,Old!A:C,MATCH($B$1,Old!$1:$1,0),FALSE)=B2,VLOOKUP(A2,Old!A:C,MATCH($C$1,Old!$1:$1,0),FALSE)=C2),"NO","YES")

The difference between this and the last one is the use of MATCH($B$1,Old!$1:$1,0) to find the column (using $s to anchor the lookup values)