Compare two Sheet (same criteria one column) and Create new sheet with matches

[jmery24]

Hello againThank you first of all.

I need to compare 2 sheets, which have a single data in common and with that data create a new sheet.
Nro TKT in both sheets is a KEY to find matches

For example.
Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Nro TKt - Column A[/TD]
[TD]Name - Column B[/TD]
[TD]Fecha - Column C[/TD]
[TD]Total - Column D[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Juan[/TD]
[TD]25/05/18[/TD]
[TD]1500.00[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD]Pablo[/TD]
[TD]25/05/18[/TD]
[TD]1800.00[/TD]
[/TR]
[TR]
[TD]234567[/TD]
[TD]Pedro[/TD]
[TD]26/05/18[/TD]
[TD]-5000.00[/TD]
[/TR]
[TR]
[TD]985285[/TD]
[TD]John[/TD]
[TD]27/05/18[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]985286[/TD]
[TD]MAteo[/TD]
[TD]30/05/18[/TD]
[TD]-120[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Nro TKt - Column A[/TD]
[TD]Cia - Column B[/TD]
[TD]Name - Column C[/TD]
[TD]Fecha - Column D[/TD]
[TD]Total - Column E[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]45[/TD]
[TD]Juan[/TD]
[TD]25/05/18[/TD]
[TD]1500.00[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD]44[/TD]
[TD]Pablo[/TD]
[TD]25/05/18[/TD]
[TD]1750.00[/TD]
[/TR]
[TR]
[TD]234566[/TD]
[TD]45[/TD]
[TD]Maria[/TD]
[TD]26/05/18[/TD]
[TD]-4500.00[/TD]
[/TR]
[TR]
[TD]985285[/TD]
[TD]230[/TD]
[TD]John[/TD]
[TD]27/05/18[/TD]
[TD]-300.00[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD]55[/TD]
[TD]Carlos[/TD]
[TD]25/05/18[/TD]
[TD]12500.00[/TD]
[/TR]
[TR]
[TD]456789[/TD]
[TD]220[/TD]
[TD]Estela[/TD]
[TD]26/05/18[/TD]
[TD]135500.00[/TD]
[/TR]
</tbody>[/TABLE]


Expects Results
Create Sheet3
DIF (columnB) = Total.Sheet1 - Total.Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Match - New - Column A[/TD]
[TD]Dif - New - Column B[/TD]
[TD]Nro Tkt - Sheet1 - Column C [/TD]
[TD]Name - Sheet1 - Column D[/TD]
[TD]Fecha - Sheet1 - Column E[/TD]
[TD]Total - Sheet1 - Column F
[/TD]
[TD]Nro Tkt - Sheet2 - Column G[/TD]
[TD]Cia - Sheet2 - Column H[/TD]
[TD]Name - Sheet2 - Column I[/TD]
[TD]Fecha - Sheet2 - Column J[/TD]
[TD]Total - Sheet2 - Column K[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD]0.00[/TD]
[TD]123456[/TD]
[TD]Juan[/TD]
[TD]25/05/18[/TD]
[TD]1500.00[/TD]
[TD]123456[/TD]
[TD]45[/TD]
[TD]Juan[/TD]
[TD]25/05/18[/TD]
[TD]1500.00[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD]50.00[/TD]
[TD]123457[/TD]
[TD]Pablo[/TD]
[TD]25/05/18[/TD]
[TD]1800.00[/TD]
[TD]123457[/TD]
[TD]44[/TD]
[TD]Pablo[/TD]
[TD]25/05/18[/TD]
[TD]1750.00[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]-5000.00[/TD]
[TD]234567[/TD]
[TD]Pedro[/TD]
[TD]26/05/18[/TD]
[TD]-5000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD]300.00[/TD]
[TD]985285[/TD]
[TD]John[/TD]
[TD]27/05/18[/TD]
[TD]0.00[/TD]
[TD]985285[/TD]
[TD]230[/TD]
[TD]John[/TD]
[TD]27/05/18[/TD]
[TD]-300[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]-120.00[/TD]
[TD]985286[/TD]
[TD]Mateo[/TD]
[TD]30/05/18[/TD]
[TD]-120.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]-12500.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123458[/TD]
[TD]55[/TD]
[TD]Carlos[/TD]
[TD]25/05/18[/TD]
[TD]12500.00[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]-135500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]456789[/TD]
[TD]220[/TD]
[TD]Estela[/TD]
[TD]26/05/18[/TD]
[TD]135500.00[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]4500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]234566[/TD]
[TD]45[/TD]
[TD]Maria[/TD]
[TD]26/05/18[/TD]
[TD]-4500.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
I do not know how to start
Juan Manuel

 

[sandy666]

if you allowed to use PowerQuery (2010/2013 add-in, 2016 built-in) you can try load two table into PQ then merge them by col.A with FullOuter, change null to 0, add custom column for differences and add conditional column to compare columns A (sheet1) and column A (sheet2)

let
    Source = Table.NestedJoin(Table1,{"Nro TKt - Column A"},Table2,{"Nro TKt - Column A"},"Table2",JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Nro TKt - Column A", "Cia - Column B", "Name - Column C", "Fecha - Column D", "Total - Column E"}, {"Table2.Nro TKt - Column A", "Table2.Cia - Column B", "Table2.Name - Column C", "Table2.Fecha - Column D", "Table2.Total - Column E"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,0,Replacer.ReplaceValue,{"Total - Column D"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Table2.Total - Column E"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Diff", each [#"Total - Column D"]-[#"Table2.Total - Column E"]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Diff", "Nro TKt - Column A", "Name - Column B", "Fecha - Column C", "Total - Column D", "Table2.Nro TKt - Column A", "Table2.Cia - Column B", "Table2.Name - Column C", "Table2.Fecha - Column D", "Table2.Total - Column E"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Match", each if [#"Nro TKt - Column A"] = [#"Table2.Nro TKt - Column A"] then "OK" else "NO"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Match", "Diff", "Nro TKt - Column A", "Name - Column B", "Fecha - Column C", "Total - Column D", "Table2.Nro TKt - Column A", "Table2.Cia - Column B", "Table2.Name - Column C", "Table2.Fecha - Column D", "Table2.Total - Column E"})
in
    #"Reordered Columns1"

with the result:

[TABLE="class: head"]
<tbody>[TR="bgcolor: #FFFFFF"]
[TD]Match[/TD]
[TD]Diff[/TD]
[TD]Nro TKt - Column A[/TD]
[TD]Name - Column B[/TD]
[TD]Fecha - Column C[/TD]
[TD]Total - Column D[/TD]
[TD]Table2.Nro TKt - Column A[/TD]
[TD]Table2.Cia - Column B[/TD]
[TD]Table2.Name - Column C[/TD]
[TD]Table2.Fecha - Column D[/TD]
[TD]Table2.Total - Column E[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]OK[/TD]
[TD]

0​

[/TD]
[TD]

123456​

[/TD]
[TD]Juan[/TD]
[TD]

25/05/2018​

[/TD]
[TD]

1500​

[/TD]
[TD]

123456​

[/TD]
[TD]

45​

[/TD]
[TD]Juan[/TD]
[TD]

25/05/2018​

[/TD]
[TD]

1500​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]OK[/TD]
[TD]

50​

[/TD]
[TD]

123457​

[/TD]
[TD]Pablo[/TD]
[TD]

25/05/2018​

[/TD]
[TD]

1800​

[/TD]
[TD]

123457​

[/TD]
[TD]

44​

[/TD]
[TD]Pablo[/TD]
[TD]

25/05/2018​

[/TD]
[TD]

1750​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]NO[/TD]
[TD]

4500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

234566​

[/TD]
[TD]

45​

[/TD]
[TD]Maria[/TD]
[TD]

26/05/2018​

[/TD]
[TD]

-4500​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]OK[/TD]
[TD]

300​

[/TD]
[TD]

985285​

[/TD]
[TD]John[/TD]
[TD]

27/05/2018​

[/TD]
[TD]

0​

[/TD]
[TD]

985285​

[/TD]
[TD]

230​

[/TD]
[TD]John[/TD]
[TD]

27/05/2018​

[/TD]
[TD]

-300​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]NO[/TD]
[TD]

-12500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

123458​

[/TD]
[TD]

55​

[/TD]
[TD]Carlos[/TD]
[TD]

25/05/2018​

[/TD]
[TD]

12500​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]NO[/TD]
[TD]

-135500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

456789​

[/TD]
[TD]

220​

[/TD]
[TD]Estela[/TD]
[TD]

26/05/2018​

[/TD]
[TD]

135500​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]NO[/TD]
[TD]

-5000​

[/TD]
[TD]

234567​

[/TD]
[TD]Pedro[/TD]
[TD]

26/05/2018​

[/TD]
[TD]

-5000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD]NO[/TD]
[TD]

-120​

[/TD]
[TD]

985286​

[/TD]
[TD]MAteo[/TD]
[TD]

30/05/2018​

[/TD]
[TD]

-120​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #70AD47"][/TD]
[TD="bgcolor: #70AD47"][/TD]
[TD="bgcolor: #70AD47"][/TD]
[TD="bgcolor: #70AD47"][/TD]
[TD="bgcolor: #70AD47"][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]