Formula to compare 2 sets of data using the date?

**murphy** · 03-09-2011, 04:34 PM

Hi all,

I have 2 worksheets of what should be identical data and I want to check that the dates on both correspond.

Basically I have 3 columns of data thousands of rows long with information in cell A and B with the date in column C (I have 2 sets of this data but the date may have been edited in one at some point didn't get updated in the other)

I know columns A and B are identical as they never change, all the data in cells A & B correspond exactly (As in cell A1500 is the same as B1500)
The date in column C is the only one that may have been altered.

Is there a formula that I can use on a separate worksheet?
Maybe put the data from worksheet 1 in columns A,B & C and the data from worksheet 2 in columns D,E & F and use a pull-down formula in column G?
And how would I identify a discrepancy if there was one?

Also, there are multiple instances of the same date so would cells in column A and C maybe have to used to compare with cells in D and F rather than just using the date columns (C and F) for comparison?

I hope that's not too confusing!!

I have 2003 and 2007

Any help is very much appreciated.

Thanks

**ChemistB** · 03-09-2011, 04:44 PM

Questions:
You say that the value in A is equal to value in B? (i.e. A2 = Car, B2 = Car)?
Do any of the records repeat in any way? for example, is every entry in Col A unique (i.e. it won't appear in row 3, then again in 295)?
OR, are the combinations of Col A and B unique (i.e. Carl might appear multiple times in Col A but is only paired with Car in Col B once)?

**murphy** · 03-09-2011, 05:13 PM

Thanks for your reply,

Values in column A do not repeat, no 2 cells have the same value, same goes for values in column B.
In fact, All values in the cells in columns A and B are unique.

**ChemistB** · 03-10-2011, 11:05 AM

Okay, lets say you have book1 and book2 with data on sheet1 in each. I suggest in book1 column D, we return the date from book2 so you can see at a glance the two dates. In Column E, we can compare the two dates so you have an easy filter. Does that work for you?

In D2 dragged down
=INDEX([book2]sheet1!$C$2:$C$10000, MATCH(A2, [book2]sheet1!$A$2:$A$10000)
You will get an error, if there is not a match for A2 in the other book.

In E2 dragged down
=IF(D2=C2, "Match", "")
You can filter on column E and bring up all the blanks (non-matches) or matches.

**murphy** · 03-10-2011, 04:05 PM

Thanks ChemistB,

I will just use a variation of your =IF(D2=C2, "Match", "") by 'Sort Ascending' using column A in both sheets, then put both sets of values side by side on a new sheet and alter =IF(D2=C2, "Match", "") to suit. Works great for me!

Thanks for your time, much appreciated!