Comparing two tables for accuracy date and employee time

[ltgreat]

I'm trying to figure a way to compare two tables for accuracy that are generated for employees tracking their time worked. These are completed at two different locations and are hundreds long making daily comparisions cumbersome to say the least. All I'm after is a way to compare these for accuracy and if there is a descripsency make it easily identifiable. I've included an example of these two tables below. Thanks for any help!

	1/1/2017	1/2/2017	1/3/2017	1/4/2017	1/5/2017
John Smith	Overtime	Straight	Off	Straight	Off
Jane Smith	Off	Overtime	Overtime	Straight	Off
David Jones	Straight	Off	Off	Straight	Overtime

<tbody>
</tbody><colgroup><col><col><col><col span="3"></colgroup>

Name	Date	Desc
John Smith	1/1/2017	Overtime
Jane Smith	1/2/2017	Off
David Jones	1/1/2017	Straight
John Smith	1/2/2017	Straight
Jane Smith	1/3/2017	Overtime
David Jones	1/3/2017	Off
John Smith	1/4/2017	Straight
Jane Smith	1/4/2017	Straight
David Jones	1/4/2017	Straight
John Smith	1/5/2017	Off
Jane Smith	1/5/2017	Off
David Jones	1/5/2017	Overtime

<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="64" style="width: 48pt;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <tbody> </tbody>

 

[cerfani]

You need the microsoft cloud xD

sorry but those commercials are so annoying and this made me think of it xD

i will assume you want to just write a formula next to the table that will tell you something like true or false for accurate or not and both tables start at A1...

if that 2nd table in D1, label the column "Accurate?"

in D2 write this formula...


=C2=INDEX(Table1Sheet!$B$2:$F$4,MATCH(A2,Table1Sheet!$A$2:$A4$,0),MATCH(B2,Table1Sheet!$B$1:$F$1,0))

that will lookup the value on table 1 for the corresponding employee and date and compare with the record on the row your formula is on

 

[ltgreat]

LOL those commercials....

So tried your fourmla (wow... need a course in excel) and got about half not available and half with answers but one seems to be wrong. I've put both on one page for simplicity sake and included the results with the formula below. THoughts?

Name	Date	Desc
John Smith	1/1/2017	Overtime	#N/A
Jane Smith	1/2/2017	Off	#N/A
David Jones	1/1/2017	Straight	#N/A
John Smith	1/2/2017	Straight	#N/A
Jane Smith	1/3/2017	Overtime	#N/A
David Jones	1/3/2017	Off	#N/A
John Smith	1/4/2017	Straight	TRUE
Jane Smith	1/4/2017	Straight	FALSE
David Jones	1/4/2017	Straight	TRUE
John Smith	1/5/2017	Off	TRUE
Jane Smith	1/5/2017	Off	TRUE
David Jones	1/5/2017	Overtime	TRUE

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

=C3=INDEX($B$2:$F$4,MATCH(A3,$A$2:$A$4,0),MATCH(B3,$B$1:$F$1,0))

 

[cerfani]

Just make sure you are referencing the values to lookup correctly (in above formula you have C3, A3, B3 being compared to the table in B2:F4)

I know this is wrong because that 2nd table must be located somewhere else... i believe that is the issue and when you drag it down it hits the actual table so your results are offset

 

[cerfani]

if you look on your results, that very first true on the row for John Smith 1/4/2017 is actually referencing the first record for John Smith on 1/1/2017 and that false on the next row is comparing Jane Smith's data for 1/2/2017 and she wrote Overtime when it was supposed to be Off.

Which means you pasted that table on row 8 and the first record is actually row 9 and the formula should be on D9...

=C9=INDEX($B$2:$F$4,MATCH(A9,$A$2:$A$4,0),MATCH(B9,$B$1:$F$1,0))

(you were 6 rows off it looks like)