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.

Employee Name	Hours Worked
Smith, John	5
Brown, James	10

<tbody>
</tbody>

Sheet 1

User Name	Hours Entered
Smith, John	5
Brown, James	11

<tbody>
</tbody>

Sheet 2

Employee Name	Hours Worked	Hours Entered
Smith, John	5	5
Brown, James	10	11

<tbody>
</tbody>

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

Employee Name	Hours Worked		User Name	Hours Entered		Employee Name	HW	HE
Smith, John	5​		Smith, John	5​		Smith, John	5	5
Brown, James	10​		Brown, James	11​		Brown, James	10	11

[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)

Employee Name	Hours Worked		User Name	Hours Entered		Sum of Value	Column
Smith, John	5​		Smith, John	5​		Row	Hours Entered	Hours Worked
Brown, James	10​		Brown, James	11​		Brown, James	11​	10​
						Smith, John	5​	5​