Formulas Needed for Report Comparison

[Hscott1982]

Hello lovely Excel friends...I need help. Getting back into my daily excel use and trying to dust off my fomulas knowledge.

I have 2 reports in Excel that I need to cross compare. Both reports have Employee Names and ID numbers, both have varying dates of attendance incidents. I need to compare to see the differences of the dates.

Report 1 is the Master Report, Report 2 is for Verification.

Thoughts: How can I compare the dates across reports for each Employee and point out the differences?

Thank you in advance for your amazing help!

 

[Sufiyan97]

Hi & Welcome to MrExcel

Please post your sample data using below link

 

[Hscott1982]

Testing Attendance Formulas.xlsx
	K
13
Warren

 

[Sufiyan97]

The sheet you posted is black, please upload again.

 

[Hscott1982]

The sheet you posted is black, please upload again.

Hello,
I tried the link you provided, what I responded above is what it gave me. This is a work computer, so somethings are locked down.

 

[Fluff]

You need to select a range of cells before clicking "mini sheet", not just a single cell.

 

[Hscott1982]

Tab 1 "Warren"

Testing Attendance Formulas.xlsx
	A	B	C	D	E	F	G
1	Employee Number	Incident DATE	CREW	DEPT	CLOCK#	Teammate	FULL or HALF
2	10101435	11/4/2019	D	131	1206	Fulton, Aaron	Unexcused - Half Incident
3	10101435	8/10/2020	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
4	10101435	8/28/2020	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
5	10101435	10/11/2020	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
6	10101435	9/10/2021	D	131	1206	Fulton, Aaron	Unexcused - Half Incident
7	10101435	3/28/2021	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
8	10101435	4/14/2021	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
9	10101435	5/17/2021	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
10	10101435	10/19/2021	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
11	10101435	11/15/2021	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
12	10101435	2/7/2022	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
13	10101435	4/28/2022	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
14	10101435	8/4/2022	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
15	10101435	8/31/2022	D	131	1206	Fulton, Aaron	Unexcused - Full Incident
16	10099174	1/20/2020	A	131	1182	Hawkins, Aaron	Unexcused - Full Incident
17	10099174	7/12/2020	A	131	1182	Hawkins, Aaron	Unexcused - Full Incident
18	10099174	12/28/2020	B	131	1182	Hawkins, Aaron	Unexcused - Full Incident
19	10099174	9/4/2021	A	131	1182	Hawkins, Aaron	Unexcused - Full Incident
20	10099174	6/16/2022	A	131	1182	Hawkins, Aaron	Unexcused - Full Incident
21	10153797	11/4/2019	D	131	1423	Lowe, Aaron	Unexcused - Half Incident
22	10153797	12/28/2019	C	131	1423	Lowe, Aaron	Unexcused - Full Incident
23	10153797	3/3/2020	C	131	1423	Lowe, Aaron	Unexcused - Full Incident
24	10153797	6/13/2020	C	131	1423	Lowe, Aaron	Unexcused - Full Incident
25	10153797	9/19/2020	C	131	1423	Lowe, Aaron	Unexcused - Full Incident
26	10153797	10/23/2020	D	131	1423	Lowe, Aaron	Unexcused - Full Incident
27	10153797	9/25/2021	D	123	1423	Lowe, Aaron	Unexcused - Half Incident
28	10153797	11/25/2021	D	123	1423	Lowe, Aaron	Unexcused - Half Incident
29	10153797	2/14/2021	D	123	1423	Lowe, Aaron	Unexcused - Full Incident
30	10153797	3/27/2021	D	123	1423	Lowe, Aaron	Unexcused - Full Incident
31	10153797	5/8/2021	D	123	1423	Lowe, Aaron	Unexcused - Full Incident
32	10153797	1/21/2020	C	131	1423	Lowe, Aaron	Unexcused - Half Incident
33	10153797	7/11/2020	C	131	1423	Lowe, Aaron	Unexcused - Half Incident
34	10153797	10/30/2021	C	123	1423	Lowe, Aaron	Unexcused - Full Incident
35	10153797	2/24/2022	A	123	1423	Lowe, Aaron	Unexcused - Full Incident
36	10186040	7/11/2020	C	169	1643	Moore, Aaron	Unexcused - Full Incident
37	10186040	3/6/2021	C	169	1643	Moore, Aaron	Unexcused - Half Incident
38	10186040	9/26/2020	B	169	1643	Moore, Aaron	Unexcused - Full Incident
39	10186040	10/2/2021	C	169	1643	Moore, Aaron	Unexcused - Half Incident
40	10186040	1/13/2021	C	169	1643	Moore, Aaron	Unexcused - Full Incident
41	10186040	9/25/2021	D	169	1643	Moore, Aaron	Unexcused - Full Incident
42	10186040	7/14/2021	C	169	1643	Moore, Aaron	Unexcused - Half Incident
43	10186040	2/10/2022	C	169	1643	Moore, Aaron	Unexcused - Full Incident
44	10186040	3/17/2022	D	169	1643	Moore, Aaron	Unexcused - Full Incident
45	10186040	6/6/2022	C	131	1643	Moore, Aaron	Unexcused - Full Incident
46	10186040	8/11/2022	C	131	1643	Moore, Aaron	Unexcused - Full Incident
Warren

Tab 2 "Dayforce"

Testing Attendance Formulas.xlsx
	A	B	C	D	E	F	G
1	Employee Number	Teammate	Employment Status	Pay Type	Incident Date	Incident Name	Attendance Points
2	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	8/10/2020	Attendance History 1pt	1.00000
3	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	8/28/2020	Attendance History 1pt	1.00000
4	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	10/11/2020	Attendance History 1pt	1.00000
5	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	3/28/2021	Attendance History 1pt	1.00000
6	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	4/14/2021	Attendance History 1pt	1.00000
7	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	5/17/2021	Attendance History 1pt	1.00000
8	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	9/10/2021	Attendance History .5pt	0.50000
9	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	10/19/2021	Attendance History 1pt	1.00000
10	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/15/2021	Attendance History 1pt	1.00000
11	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	2/7/2022	Attendance History 1pt	1.00000
12	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	4/28/2022	Attendance History 1pt	1.00000
13	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	8/4/2022	Attendance History 1pt	1.00000
14	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	8/31/2022	Attendance History 1pt	1.00000
15	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/10/2022	Hours Reduction (0P)	0.00000
16	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/14/2022	Hours Reduction (0P)	0.00000
17	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/19/2022	Hours Reduction (0P)	0.00000
18	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/20/2022	Hours Reduction (0P)	0.00000
19	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/23/2022	Hours Reduction (0P)	0.00000
20	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/28/2022	Hours Reduction (0P)	0.00000
21	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	11/29/2022	Absence	1.00000
22	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	12/4/2022	Hours Reduction (0P)	0.00000
23	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	12/16/2022	Hours Reduction (0P)	0.00000
24	10101435	Fulton, Aaron	Active	Hourly Non-Exempt	12/22/2022	Hours Reduction (0P)	0.00000
25	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	1/20/2020	Attendance History 1pt	1.00000
26	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	7/12/2020	Attendance History 1pt	1.00000
27	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	12/28/2020	Attendance History 1pt	1.00000
28	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	9/4/2021	Attendance History 1pt	1.00000
29	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	6/16/2022	Attendance History 1pt	1.00000
30	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	11/7/2022	Hours Reduction (0P)	0.00000
31	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	11/13/2022	Hours Reduction (0P)	0.00000
32	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	11/16/2022	Hours Reduction (0P)	0.00000
33	10099174	Hawkins, Aaron	Active	Hourly Non-Exempt	12/19/2022	Hours Reduction (0P)	0.00000
34	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	1/21/2020	Attendance History .5pt	0.50000
35	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	3/3/2020	Attendance History 1pt	1.00000
36	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	6/13/2020	Attendance History 1pt	1.00000
37	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	7/11/2020	Attendance History .5pt	0.50000
38	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	9/19/2020	Attendance History 1pt	1.00000
39	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	10/23/2020	Attendance History 1pt	1.00000
40	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	2/14/2021	Attendance History 1pt	1.00000
41	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	3/27/2021	Attendance History 1pt	1.00000
42	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	5/8/2021	Attendance History 1pt	1.00000
43	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	9/25/2021	Attendance History .5pt	0.50000
44	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	10/30/2021	Attendance History 1pt	1.00000
45	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	11/25/2021	Attendance History .5pt	0.50000
46	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	2/24/2022	Attendance History 1pt	1.00000
47	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	10/29/2022	Attendance History 1pt	1.00000
48	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	11/23/2022	Hours Reduction (0P)	0.00000
49	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	11/25/2022	Hours Reduction (0P)	0.00000
50	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	11/29/2022	Hours Reduction (0P)	0.00000
51	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	12/9/2022	Hours Reduction (0P)	0.00000
52	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	12/10/2022	Hours Reduction (0P)	0.00000
53	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	12/11/2022	Hours Reduction (0P)	0.00000
54	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	12/13/2022	Hours Reduction (0P)	0.00000
55	10153797	Lowe, Aaron	Active	Hourly Non-Exempt	12/20/2022	Hours Reduction (0P)	0.00000
56	10186040	Moore, Aaron	Active	Hourly Non-Exempt	7/11/2020	Attendance History 1pt	1.00000
57	10186040	Moore, Aaron	Active	Hourly Non-Exempt	9/26/2020	Attendance History 1pt	1.00000
58	10186040	Moore, Aaron	Active	Hourly Non-Exempt	1/13/2021	Attendance History 1pt	1.00000
59	10186040	Moore, Aaron	Active	Hourly Non-Exempt	3/6/2021	Attendance History .5pt	0.50000
60	10186040	Moore, Aaron	Active	Hourly Non-Exempt	7/14/2021	Attendance History .5pt	0.50000
61	10186040	Moore, Aaron	Active	Hourly Non-Exempt	9/25/2021	Attendance History 1pt	1.00000
62	10186040	Moore, Aaron	Active	Hourly Non-Exempt	10/2/2021	Attendance History .5pt	0.50000
63	10186040	Moore, Aaron	Active	Hourly Non-Exempt	2/10/2022	Attendance History 1pt	1.00000
64	10186040	Moore, Aaron	Active	Hourly Non-Exempt	3/17/2022	Attendance History 1pt	1.00000
65	10186040	Moore, Aaron	Active	Hourly Non-Exempt	6/6/2022	Attendance History 1pt	1.00000
66	10186040	Moore, Aaron	Active	Hourly Non-Exempt	8/11/2022	Attendance History 1pt	1.00000
67	10186040	Moore, Aaron	Active	Hourly Non-Exempt	11/30/2022	Hours Reduction (0P)	0.00000
68	10186040	Moore, Aaron	Active	Hourly Non-Exempt	12/11/2022	Absence	1.00000
69	10186040	Moore, Aaron	Active	Hourly Non-Exempt	12/19/2022	Hours Reduction (0P)	0.00000
DayForce

 

[Alex Blakenburg]

It depends a little on how you want the results to look, which will probably depend on what your next steps are after identifying the differences.
Power Query will be my choice for giving you a list of items in Warren and not in Dayforce and a list for or items in Daysforce and not in Warren.
It can also give you a full list showing empty data where the data doesn't exist in either list.

You could also do an formula on Warren looking up DayForce identifying missing and one of Dayforce looking up Warrent to identify missing.
(you would need to address the issue that your date in Warren is right aligned and recognised as a date and the date in DayForce is left aligned which indicates it may be stored as text)

VBA is also an option but you would need to be much more specific on what you want your output to look like.

Note: Difference based on the information provided simply means in one list and not in the other.

 

[Hscott1982]

I really would just like it to cross reference each employee and highlight any differences of the attendance dates on the Dayforce report.