Comparing Spreadsheets For Same Values

[shellhutchings]

I have 2 spreadsheets I need to compare data against. I have 4 columns on each sheet of which A is employee number, B is start date, C end date and D % of sales for period specified between the B & C. Column A has multiple employee numbers and the same employee number is present several times due to the dates. Ie employee no, 123456, start 02.07.22 - end 08.09.22 46%, then 123456, 09.09.22 - 01.12.22 33%. Which formula could I use to compare spreadsheets that the employee number on the start and end date specified has the same sales % on each so I can look for any discrepancies between the two sheets? Thank you.

 

[Wookiee]

I do a lot of spreadsheet comparisons, and my recommendation is to do this:

For each sheet, insert a new Column A. Enter "UNIQUE" in A1 and this formula in A2 and copy down to the end of your data:

=$B2&$C2&$D2&$E2

On your second sheet, do the same thing.

Then, on a blank column at the end of your data enter a modified version of this formula (I used Shell1 and Shell2 as my testing sheet names, so I entered this formula in Cell F2 and copied it down):

=XLOOKUP($A2,Shell1!$A$2:$A$8,Shell1!$E$2:$E$8,0,0)

Depending on the amount of records you have to compare, you might be able to visually verify them, but you can also add this formula in cell G2 and copy down, then filter to any FALSE values to identify mismatched sales figures.

=$E2=$F2

Hope that helps!

 

[shellhutchings]

I do a lot of spreadsheet comparisons, and my recommendation is to do this:

For each sheet, insert a new Column A. Enter "UNIQUE" in A1 and this formula in A2 and copy down to the end of your data:

=$B2&$C2&$D2&$E2

On your second sheet, do the same thing.

Then, on a blank column at the end of your data enter a modified version of this formula (I used Shell1 and Shell2 as my testing sheet names, so I entered this formula in Cell F2 and copied it down):

=XLOOKUP($A2,Shell1!$A$2:$A$8,Shell1!$E$2:$E$8,0,0)

Depending on the amount of records you have to compare, you might be able to visually verify them, but you can also add this formula in cell G2 and copy down, then filter to any FALSE values to identify mismatched sales figures.

=$E2=$F2

Hope that helps!

That's fantastic! Thank you so so much