Crosscheck 2 excel fiels and output

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

Hi,

I have 2 Excel files as find attached. I want to compare Excel1 with Excel2 and has the output as the file Output.xlsx. Kindly help!

Excel 1:

Excel 2:

Desired Output:

Thanks!

Excel1.xlsx (8.0 KB)
Excel2.xlsx (8.6 KB)

[rikulsilva (Henrique Lima da Silva)]

Hi @Yudhisteer_Chintaram1

Can you elaborate a little more about your requirements ?

[Anil_G (Anil Gorthi)]

@Yudhisteer_Chintaram1

1. Reqd both excels into dt1 and dt2
2. Use join datatable activity with inner type join and join on invoice number and po number both
3. After that use a filter datatable and give the column names you want only to filter out the extra po number and invoice number columns
4. Use a invoke code activity and send joineddt as in/out variable and inside use the below code to fill pending

Joineddt.AsEnumerable.Where(function(x) x("Payment Status").ToString).ToList.ForEach(Sub(r) r("Payment Status") = "Pending")

Cheers

[rikulsilva (Henrique Lima da Silva)]

Following a sample solution using the steps mentioned by @Anil_G

CompareExcelFile.zip (56.9 KB)

[RajKumar_DC (RajKumar Durai)]

Hi @Yudhisteer_Chintaram1 ,

Excel1

Excel2

Output

Workflow

Code

If(dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").Count>0,
dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").CopyToDataTable.Rows(0).Item("Supplier's Invoice Number").ToString+"^"+
If(String.IsNullOrWhiteSpace(dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").CopyToDataTable.Rows(0).Item("Payment Status").ToString.Trim),"Pending",dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").CopyToDataTable.Rows(0).Item("Payment Status").ToString)+"^"+
dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").CopyToDataTable.Rows(0).Item("Payment Date").ToString+"^"+
dt2.Select("[Supplier's Invoice Number]='"+CurrentRow("Col1").ToString+"'").CopyToDataTable.Rows(0).Item("Payment Reference").ToString,"").Split("^"c)

Xaml
Excel.zip (2.2 KB)

Thanks,

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

Hi @rikulsilva ,

Your flow seems to work fine except the invoke code where I am getting:

[rikulsilva (Henrique Lima da Silva)]

Hi @Yudhisteer_Chintaram1

Go to Imports Panel. Check if System.Collections.ObjectModel was imported twice. If you find duplicate import, remove one and try again

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

@rikulsilva

Yes I do:

[rikulsilva (Henrique Lima da Silva)]

Nice

Is there another workflow in the project ? If yes, you can do the same and check the import.

Another options

Remove the Invoce Code Activity

Add a new one

In argument add dt_Output as in/out, type DataTable and assign dt_Output for it

Put the expression

dt_Output.AsEnumerable.where(function(x) String.IsNullOrEmpty(x("Payment Status").ToString)).ToList().ForEach(Sub (r) r.SetField(Of String)("Payment Status", "Pending"))

Then try again

[rikulsilva (Henrique Lima da Silva)]

One more

Open the xaml in a text editor e try to find duplicate reference and remove

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

Yep. This one work. Thanks a lot!

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

@rikulsilva

Suppose in my Excel1 I have more invoice numbers but they are not found in Excel2. How can I include it in the Output.xlsx like this below with “To be checked” as payment status?

Thanks!