Identifying the difference between two data sets

R

rrmando18

New Member
Joined
Sep 13, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Happy Tuesday party people. I need help comparing two data sets to identify what is causing the difference by date, employee ID#, and WO#. I don't know if I can do this in Excel or if I need to use Power BI or something else. The two tables below show how I am receiving the data. Not sure if I need to change any of the formatting or add a helper column or something. The info below is for one job only, but we have lots of jobs that I need to do this with. There can also be multiple WO#s under one job.

Table1 is the one I need to match to. The difference can go either way. Sometimes Table 1 totals are higher and something they're lower.

In the example below, Table1 shows a total of $48,067.78 and 369.75 hours.

Table2 shows $45,114.59 and 349.75 hours.

How the heck do I go about finding what is causing the difference? Some of these tables can be very long with lots of employees and lots of WO#s.


Table1:
Timesheet Date in column A is a custom mm-dd-yyyy format.
Column B "PID" is the employee ID#, but the employee ID# are the 5 digits between ES and NTR.


Table2:
The Per Diem row can be ignored. That's how I'm receiving the data.

Thank you!


Table1.xlsx
ABCDE
1Timesheet DatePIDNet HoursTotal CostWO#
202-Jul-2024ES40214NTR4520.56938161-045
303-Jul-2024ES40214NTR101,301.40938161-045
408-Jul-2024ES38057NTR101,236.90938161-045
508-Jul-2024ES40214NTR101,301.40938161-045
608-Jul-2024ES38615NTR9.751,372.70938161-045
709-Jul-2024ES38057NTR101,236.90938161-045
809-Jul-2024ES40214NTR101,301.40938161-045
909-Jul-2024ES38615NTR101,407.90938161-045
1010-Jul-2024ES38057NTR9.751,205.98938161-045
1110-Jul-2024ES40214NTR101,301.40938161-045
1211-Jul-2024ES38057NTR101,236.90938161-045
1311-Jul-2024ES40214NTR101,301.40938161-045
1412-Jul-2024ES38057NTR0-938161-045
1512-Jul-2024ES40214NTR0-938161-045
1613-Jul-2024ES38057NTR0-938161-045
1713-Jul-2024ES40214NTR0-938161-045
1814-Jul-2024ES38057NTR0-938161-045
1914-Jul-2024ES40214NTR0-938161-045
2015-Jul-2024ES38057NTR101,236.90938161-045
2115-Jul-2024ES40214NTR91,171.26938161-045
2215-Jul-2024ES38615NTR101,407.90938161-045
2316-Jul-2024ES38057NTR101,236.90938161-045
2416-Jul-2024ES40214NTR101,301.40938161-045
2516-Jul-2024ES38615NTR101,407.90938161-045
2617-Jul-2024ES38057NTR101,236.90938161-045
2717-Jul-2024ES40214NTR101,301.40938161-045
2817-Jul-2024ES38615NTR101,407.90938161-045
2918-Jul-2024ES38057NTR101,236.90938161-045
3018-Jul-2024ES40214NTR101,301.40938161-045
3118-Jul-2024ES38615NTR101,407.90938161-045
3219-Jul-2024ES38057NTR0-938161-045
3319-Jul-2024ES40214NTR0-938161-045
3420-Jul-2024ES38057NTR0-938161-045
3520-Jul-2024ES40214NTR0-938161-045
3621-Jul-2024ES38057NTR0-938161-045
3721-Jul-2024ES40214NTR0-938161-045
3822-Jul-2024ES38057NTR101,236.90938161-045
3922-Jul-2024ES40214NTR101,301.40938161-045
4022-Jul-2024ES38615NTR101,407.90938161-045
4123-Jul-2024ES38057NTR101,236.90938161-045
4223-Jul-2024ES40214NTR101,301.40938161-045
4324-Jul-2024ES38057NTR101,236.90938161-045
4424-Jul-2024ES40214NTR81,041.12938161-045
4525-Jul-2024ES38057NTR101,236.90938161-045
4626-Jul-2024ES38057NTR0-938161-045
4727-Jul-2024ES38057NTR0-938161-045
4828-Jul-2024ES38057NTR0-938161-045
4929-Jul-2024ES40214NTR101,301.40938161-045
5029-Jul-2024ES38615NTR9.751,372.70938161-045
5130-Jul-2024ES38057NTR9.51,175.06938161-045
5230-Jul-2024ES40214NTR101,301.40938161-045
5331-Jul-2024ES38057NTR101,236.90938161-045
5431-Jul-2024ES40214NTR101,301.40938161-045
55369.7548,067.78
Sheet2
Cell Formulas
RangeFormula
C55:D55C55=SUM(C2:C54)




Table2.xlsx
ABCDEFGHIJKLM
1Calculated Totals:349.7500349.7545,114.59--45,114.59
2
3EmployeePostDateJobSTHoursOTHoursDTHours TTHours OtherHoursPerDiemSTRateOTRateDTRateWO#
4380577/8/2024240075000-0150120.48152.37191.271
5380577/8/2024240075100010.0000120.48152.37191.27 938161-045
6380577/9/2024240075000-0150120.48152.37191.271
7380577/9/2024240075100010.0000120.48152.37191.27 938161-045
8380577/10/2024240075000-0150120.48152.37191.271
9380577/10/20242400759.75009.7500120.48152.37191.27 938161-045
10380577/11/2024240075000-0150120.48152.37191.271
11380577/11/2024240075100010.0000120.48152.37191.27 938161-045
12380577/12/2024240075000-0150120.48152.37191.271
13380577/13/2024240075000-0150120.48152.37191.271
14380577/14/2024240075000-0150120.48152.37191.271
15380577/15/2024240075000-0150120.48152.37191.271
16380577/15/2024240075100010.0000120.48152.37191.27 938161-045
17380577/16/2024240075000-0150120.48152.37191.271
18380577/16/2024240075100010.0000120.48152.37191.27 938161-045
19380577/17/2024240075000-0150120.48152.37191.271
20380577/17/2024240075100010.0000120.48152.37191.27 938161-045
21380577/18/2024240075000-0150120.48152.37191.271
22380577/18/2024240075100010.0000120.48152.37191.27 938161-045
23380577/19/2024240075000-0150120.48152.37191.271
24380577/20/2024240075000-0150120.48152.37191.271
25380577/21/2024240075000-0150120.48152.37191.271
26380577/22/2024240075000-0150120.48152.37191.271
27380577/22/2024240075100010.0000120.48152.37191.27 938161-045
28380577/23/2024240075000-0150120.48152.37191.271
29380577/23/2024240075100010.0000120.48152.37191.27 938161-045
30380577/24/2024240075000-0150120.48152.37191.271
31380577/24/2024240075100010.0000120.48152.37191.27 938161-045
32380577/25/2024240075000-0150120.48152.37191.271
33380577/25/2024240075100010.0000120.48152.37191.27 938161-045
34380577/26/2024240075000-0150120.48152.37191.271
35380577/27/2024240075000-0150120.48152.37191.271
36380577/28/2024240075000-0150120.48152.37191.271
37380577/29/2024240075000-0150120.48152.37191.271
38380577/30/2024240075000-0150120.48152.37191.271
39380577/30/20242400759.5009.5000120.48152.37191.27 938161-045
40386157/8/20242400759.75009.7500140.79177.91223.21 938161-045
41386157/9/2024240075100010.0000140.79177.91223.21 938161-045
42386157/15/2024240075100010.0000140.79177.91223.21 938161-045
43386157/16/2024240075100010.0000140.79177.91223.21 938161-045
44386157/17/2024240075100010.0000140.79177.91223.21 938161-045
45386157/18/2024240075100010.0000140.79177.91223.21 938161-045
46386157/22/2024240075100010.0000140.79177.91223.21 938161-045
47386157/29/20242400759.75009.7500140.79177.91223.21 938161-045
48402147/2/2024240075000-0165130.14164.52206.461
49402147/2/20242400754004.0000130.14164.52206.46 938161-045
50402147/3/2024240075000-0165130.14164.52206.461
51402147/3/2024240075100010.0000130.14164.52206.46 938161-045
52402147/4/2024240075000-0165130.14164.52206.461
53402147/8/2024240075000-0165130.14164.52206.461
54402147/8/2024240075100010.0000130.14164.52206.46 938161-045
55402147/9/2024240075000-0165130.14164.52206.461
56402147/9/2024240075100010.0000130.14164.52206.46 938161-045
57402147/10/2024240075000-0165130.14164.52206.461
58402147/10/2024240075100010.0000130.14164.52206.46 938161-045
59402147/11/2024240075000-0165130.14164.52206.461
60402147/11/2024240075100010.0000130.14164.52206.46 938161-045
61402147/12/2024240075000-0165130.14164.52206.461
62402147/13/2024240075000-0165130.14164.52206.461
63402147/14/2024240075000-0165130.14164.52206.461
64402147/15/2024240075000-0165130.14164.52206.461
65402147/15/20242400759009.0000130.14164.52206.46 938161-045
66402147/16/2024240075000-0165130.14164.52206.461
67402147/16/2024240075100010.0000130.14164.52206.46 938161-045
68402147/17/2024240075000-0165130.14164.52206.461
69402147/17/2024240075100010.0000130.14164.52206.46 938161-045
70402147/18/2024240075000-0165130.14164.52206.461
71402147/18/2024240075100010.0000130.14164.52206.46 938161-045
72402147/19/2024240075000-0165130.14164.52206.461
73402147/20/2024240075000-0165130.14164.52206.461
74402147/21/2024240075000-0165130.14164.52206.461
75402147/22/2024240075000-0165130.14164.52206.461
76402147/22/2024240075100010.0000130.14164.52206.46 938161-045
77402147/23/2024240075000-0165130.14164.52206.461
78402147/23/2024240075100010.0000130.14164.52206.46 938161-045
79402147/24/2024240075000-0165130.14164.52206.461
80402147/24/20242400758008.0000130.14164.52206.46 938161-045
81402147/25/2024240075000-0165130.14164.52206.461
82402147/26/2024240075000-0165130.14164.52206.461
83402147/27/2024240075000-0165130.14164.52206.461
84402147/28/2024240075000-0165130.14164.52206.461
85402147/29/2024240075000-0165130.14164.52206.461
86402147/29/2024240075100010.0000130.14164.52206.46 938161-045
87402147/30/2024240075000-0165130.14164.52206.461
88402147/30/2024240075100010.0000130.14164.52206.46 938161-045
Sheet1
Cell Formulas
RangeFormula
D1:G1D1=SUM(D4:D88)
J1:L1J1=SUMPRODUCT(D4:D88,J4:J88)
M1M1=SUM(J1:L1)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Just by eyeballing it, the hours are easy, the totals not so much. If you sort by employee, then by date, you can see that 7/31/2024 did not post for employees (38057 & 40214), and that is your 20 hours. I cannot however come up with a matching dollar by trying to add those hours and the employee rates.

Not a solution to you question, just an observation.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Problem with x-axis labels in excel chart
Replies
1
Views
238
sharshra
S
C
  • Question
SUMIFS EOM
Replies
2
Views
241
ChetanPuri
C
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
433
amolvijay
A
H
  • Solved
Excel Formula - Index Match or Something Else ??
Replies
4
Views
148
hrayani
H
S
  • Solved
Calculate Backdated dates
Replies
5
Views
237
Joe4
Joe4

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top