Comparing Tables w/ Pivot Table?

[mrsbrannon]

Hello all,

I have two tables of data that is the "same" but being pulled from two difference sources. I need a way to combine this data and check for differences. The data is currently all in one workbook (but different sheets) and I would like to keep it that way. One set of data is manually entered and the other is exported from a report. Below is an example of the two sheets. The column headers do not match as the report headers are automated. If I HAVE to change the manual data (headers) to reflect the automated report, I can, but prefer not to.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Hours Worked
[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Brown, James[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]User Name[/TD]
[TD]Hours Entered[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Brown, James[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Hours Worked [/TD]
[TD]Hours Entered[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Brown, James[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
Desired Result

I feel like the answer is fairly simple, but I cannot put my finger on it. I've tried doing pivot tables with multiple consolidated data ranges, but that doesn't produce what I'm looking for. Any assistance is greatly appreciated.

Thanks.

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Employee Name[/td][td=bgcolor:#5B9BD5]Hours Worked[/td][td][/td][td=bgcolor:#5B9BD5]User Name[/td][td=bgcolor:#5B9BD5]Hours Entered[/td][td][/td][td=bgcolor:#70AD47]Employee Name[/td][td=bgcolor:#70AD47]HW[/td][td=bgcolor:#70AD47]HE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Smith, John[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#DDEBF7]Smith, John[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]Smith, John[/td][td=bgcolor:#E2EFDA]5[/td][td=bgcolor:#E2EFDA]5[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Brown, James[/td][td]

10​

[/td][td][/td][td]Brown, James[/td][td]

11​

[/td][td][/td][td]Brown, James[/td][td]10[/td][td]11[/td][/tr]
[/table]

[SIZE=1]let
    Source = Table.Combine({Table10, Table11}),
    #"Grouped Rows" = Table.Group(Source, {"Employee Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HW", each Table.Column([Count],"Hours Worked")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"HW", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "HE", each Table.Column([Count],"Hours Entered")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"HE", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Count"})
in
    #"Removed Columns"[/SIZE]

btw. in this case you need to change User Name to Employee Name. You can do that in source table or in PQ Editor

 

[sandy666]

or with PivotTable (Alt+D+P)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Employee Name[/td][td]Hours Worked[/td][td][/td][td]User Name[/td][td]Hours Entered[/td][td][/td][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7]Column[/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Smith, John[/td][td]

5​

[/td][td][/td][td]Smith, John[/td][td]

5​

[/td][td][/td][td=bgcolor:#DDEBF7]Row[/td][td=bgcolor:#DDEBF7]Hours Entered[/td][td=bgcolor:#DDEBF7]Hours Worked[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Brown, James[/td][td]

10​

[/td][td][/td][td]Brown, James[/td][td]

11​

[/td][td][/td][td]Brown, James[/td][td]

11​

[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Smith, John[/td][td]

5​

[/td][td]

5​

[/td][/tr]
[/table]