1

I’m looking for advice on how to compare two different worksheets for differences, perhaps by the highlighting of differences on one sheet. The issue is that the data set I’m comparing to is in a different format e.g. less columns and was essentially used to build the larger dataset. That’s why they should match and I want to make sure everything is consistent and there are no discrepancies.

The data itself is not too complicated - there is a date column and shipment data that matches up to it. All the dates and corresponding data should be the same as in the larger dataset.

Any advice would be appreciated, thanks in advance!

Lena278
  • 11
  • 2

2 Answers2

0

Select one column in each worksheet...

OR add one and build a unique identifier; e.g. by using =CONCATENATE() - if you need to use more than one column.

Then compare that identifier-column only, between the worksheets; using e.g. conditional formatting, or yet another column with "True/False" from the comparison.

Hannu
  • 10,568
0

If your data has some kind of unique ID like an order number, take a look at Find Items in one column that are not in another column to compare a single column. Basically just using this as a new column or conditional formatting formula:

=ISNUMBER(MATCH(A1,Sheet2!A:A,0))

Doing this for multiple columns quickly becomes cpu-intensive, but is essentially the same process. This example returns False if column A/B/C in sheet1 have no matching row in column D/E/F of sheet2. & is concatenating them together, so the order matters.

It can be extremely slow if unbounded though, like A:A instead of A2:A20 or just Table[Column] references:

=ISNUMBER(
  MATCH(
    [@ID]&[@Name]&[@Value],
    Table2[ID]&Table2[Name]&Table2[Value],0
  )
)

enter image description here

If you have a large dataset, it can be a good idea to do this one or two columns at a time and filter out what you can.

A similar example: https://stackoverflow.com/a/41219833/7411885

Cpt.Whale
  • 10,914