To Compare two excel & get unique rows based on column

[nithya]

Hi Everyone,

I have two excel file. Both excel file contains Wage Type Text & Associate Name
I need to get the value which is available in dt1 & not in dt2
Similarly, the value avail in dt2 & not in dt1

Example
D1 File looks like attached screenshot

D2 file is attached below:

The highlighted value are available only in dt2 but not in dt1 . I need to get the highlighted value as output.

Please, suggest solution for this scenario

[Suraj_B_Shetty (Suraj)]

Hi @nithya

Keep only the wage type text and associate name column in the both input table( remove the other columns) and use the following query.
dt_output1=if(dt_input1.AsEnumerable.Except(dt_input2.AsEnumerable,system.data.DataRowComparer.Default).any,dt_input1.AsEnumerable.Except(dt_input2.AsEnumerable,system.data.DataRowComparer.Default).CopyToDataTable,dt_input1.Clone) //Returns ID available only in the first table.

dt_output2=if(dt_input2.AsEnumerable.Except(dt_input1.AsEnumerable,system.data.DataRowComparer.Default).any,dt_input2.AsEnumerable.Except(dt_input1.AsEnumerable,system.data.DataRowComparer.Default).CopyToDataTable,dt_input2.Clone) //Returns ID available only in the second table.

Hope this helps

[nithya]

Hi @Suraj_B_Shetty
Thanks for the reply.

I need to add column (A, B, D, E,G ) in Output file but based on the Column B & E Comparison.

[Suraj_B_Shetty (Suraj)]

@nithya

Give a try to this.

Keep A, B, D, E and G column in the both input table
and then use the same query.

[nithya]

Hi @Suraj_B_Shetty
please, find the output below.

Since, its comparing with Amount also . The highlighted row should not be avil in output file because with same wage type & associate name both dt1 & dt2 contains.

Note – The comparison between two file should be based on Column B & D only not with Amount column

[Suraj_B_Shetty (Suraj)]

@nithya
Then keep only two column and use the same query. Then join the dt_output2 with the dt_input2 to get the remaining data.

U can use left join and then remove duplicate column from the output table.

[Suraj_B_Shetty (Suraj)]

@Nitya

Can u share input file here.

[nithya]

Sure,
Please refer to sheet 2.

SL Wage Type PP4.xlsx (9.4 KB)
SL Wage Type PP5.xlsx (10.0 KB)

[Suraj_B_Shetty (Suraj)]

Hi @nithya

Check this sample workflow
JoinEntireRow.xaml (10.5 KB)

Output
SL Wage Type PP5.xlsx (11.3 KB)

[nithya]

Thanks @Suraj_B_Shetty
It worked.