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

Nro TKt - Column A	Name - Column B	Fecha - Column C	Total - Column D
123456	Juan	25/05/18	1500.00
123457	Pablo	25/05/18	1800.00
234567	Pedro	26/05/18	-5000.00
985285	John	27/05/18	0.00
985286	MAteo	30/05/18	-120

<tbody>
</tbody>

Sheet2

Nro TKt - Column A	Cia - Column B	Name - Column C	Fecha - Column D	Total - Column E
123456	45	Juan	25/05/18	1500.00
123457	44	Pablo	25/05/18	1750.00
234566	45	Maria	26/05/18	-4500.00
985285	230	John	27/05/18	-300.00
123458	55	Carlos	25/05/18	12500.00
456789	220	Estela	26/05/18	135500.00

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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:

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
OK	0​	123456​	Juan	25/05/2018​	1500​	123456​	45​	Juan	25/05/2018​	1500​
OK	50​	123457​	Pablo	25/05/2018​	1800​	123457​	44​	Pablo	25/05/2018​	1750​
NO	4500​				0​	234566​	45​	Maria	26/05/2018​	-4500​
OK	300​	985285​	John	27/05/2018​	0​	985285​	230​	John	27/05/2018​	-300​
NO	-12500​				0​	123458​	55​	Carlos	25/05/2018​	12500​
NO	-135500​				0​	456789​	220​	Estela	26/05/2018​	135500​
NO	-5000​	234567​	Pedro	26/05/2018​	-5000​					0​
NO	-120​	985286​	MAteo	30/05/2018​	-120​					0​

<tbody>
</tbody>

<tbody>
</tbody>