I want to compare two excel sheets and take only those rows who's specific column values are same

Hello,
I want to compare two excel sheets, and want only those row values who’s one column ( ex . Sheet 1 Column - " INV NO. " ) and Sheet 2 Column - " GST Invoice Number " ) values are same.

Output - only those row Values whose column values are same and farther enter those values in SAP Application.

want to take entire row of similar column values of column (" INV NO. " & " GST Invoice Number " )

hi @Ajinya_jorwekar

Use JOIN datatable activity
type use inner Join

Hope this helps

Hi @Ajinya_jorwekar - Try below steps

→ Read the Sheet1 data to a data table (DT1)
→ Read the Sheet2 data to a data table (DT2)
→ Take assign activity

FinalDT (Datatable variable) = DT1.AsEnumerable.Where(Function(x) DT2.AsEnumerable.Where(Function(y) x(0).ToString.Equals(y(0).ToString)).Any).CopyToDataTable

→ x(0), 0 represents column index of sheet1, please change this accordingly or give column name Ex: x(“PartNumber”). Same applies to y(0) but here 0 represents the column index of Sheet2

Hello @Ajinya_jorwekar ,
You can read the Two Excel sheets using two Read range workbook activities and store the data in 2 data tables and use the join datatable activity. The screenshot of the join dataTable condition is given below:
image
I hope this helps you, If you have any query , do contact.
Regards;)

It is not giving me expected output,
Actually I want the values of “sheet 1” whose column (INV NO.) values are matching with column (GST Invoice Number)“ZEX34 table” Sheet

As a output I want “Sheet 1” values

You can use the Filter DataTable Acitivity and remove the Columns of the Sheet “ZEX34 table” in the Output of the Join DataTable:
image
Regards;)

It gives an Error, how to solve this ?

Hello @Ajinya_jorwekar ,
This error is occured because in your assign activity, the Source dataTable that you have used ‘dt1’ does not contain any data Row.

Hi @Ajinya_jorwekar
you can try this xaml it may help for you and for the output you can see the sample!

Test2.zip (9.5 KB)

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.