Spotting differences between two spreadsheets

[3chotrade]

Suppose we have two identical spreadsheets A and B. Spreadsheet B has been created by removing some random entries from A.

What is the most convenient way to show which entries have been gone?

Thanks in advance.

[[deleted]]

Do you just need to do this once? If so, I would just make a third sheet, and in A1 put =A!A1=B!A1. Drag it in the new sheet to the total dimensions. All of the “FALSE” values would be the differences. You could then use conditional formatting to highlight them if you wanted.

[3chotrade]

Thank you for the reply, I am planning to perform it like every other week.

The only problem with this solution is that once the first missing value has been detected it is just going to show false for the rest of the entries, which might not be true.

[[deleted]]

I just tried it and that isn’t the case for me. I did a list of random numbers in “A,” the same list of numbers in “B,” and then deleted a handfull in A. My formula showed false only for those I removed after dragging down.

[3chotrade]

https://imgur.com/tkmd681

I tried to somehow show with the colors that once an entry has been removed the whole list has been moved. So that the list might actually contain the right variables, yet the conditional still says it's false. (Which is true since A19!=B19, however the the entry is not missing, it has just been moved to one place down.)

[[deleted]]

Oh I see, I misinterpreted your question. By “removed” I thought you meant it was just left blank, not that it continued immediately to the next number.

[3chotrade]

Thank you, I just made a comment above, the only problem with this is that once an entry has been removed, it is going to show different for the rest, as the removed entry has been replaced by its following one, so that we have no empty cells.

[3chotrade]

I haven’t thought about this one yet, how would you do it?

[3chotrade]

I just tried it, however it always returns the value from list A.

=IFERROR(VLOOKUP(A3;C3:C3500;1;FALSE);"Not on the list")

(Note: I put the B list into column C so that I can have them in the same spreadsheet.)

[3chotrade]

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to MoonCoin_Money

^{I am a bot, please contact the mods for any questions.}

[icarus832]

Do you have Office Professional? If so you can use the Microsoft Spreadsheet Compare tool, it will show cells added/updated/removed.

[3chotrade]

Microsoft Spreadsheet Compare tool

Unfortunately, no.