How we can compare two sheets based on one reference column and get output in new sheet

[Ajinya_jorwekar]

I have two Excel Sheets, in these two sheets I have one reference column and want to compare the row data based on reference column and get that entire row data in new sheet with both sheet columns.

And also in that new sheet want to add 02 new columns

Required output like this with add two columns (Sheet 4)

How to achieve this ?

[Nguyen_Van_Luong1 (Nguyễn Văn Lương (Albert Nguyễn))]

Hi @Ajinya_jorwekar ,
Input are sheet1 and sheet2
output is sheet4
can you share expect output?

[rlgandu]

@Ajinya_jorwekar

Use Join Data Table

• Input: dtSheet1, dtSheet2
• Join Type: Inner Join
• Join Column: ReferenceColumn
• Output: dtJoined

[Brian_Mathew_Maben]

Hey @Ajinya_jorwekar ,

You can try using the ‘Join Datatable’ activity and combine rows from two tables by using columns ‘INV NO.’ and ‘GST Invoice number’

It would be helpful if you could share the excel file as well.

[Vikas_M]

Hey @Ajinya_jorwekar ,

READ Range the 2 sheets
sheet1 = dt1
sheet2 =dt2

Assign activity : dt3 =
(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(“INV NO.”).toString.trim Equals d2(“GST Invoice Number”).toString.trim
Select d1).CopyToDatatable

Hope it helps you!

[tazunnisa.badavide (Taj)]

Assuming that you are trying to join two excel sheets
In that case , Use Read rangefor 2 excel sheets
dt_1=sheet1
dt_2=sheet2
use join data table activity: input data table 1=dt_1
input data table 2=dt_2
final output:dt_final
Join type:Inner Join

use write range workbook to write the output

hope it helps, regards

[Ajinya_jorwekar]

ShippingBillDataExtraction.xlsx (13.8 KB)

Input is “Sheet 1” and " ZEX 34 Table"

Want required output is Sheet 4

[Brian_Mathew_Maben]

Hey @Ajinya_jorwekar ,

Check this workflow out
Main.xaml (7.4 KB)

Expected output

[Ajinya_jorwekar]

I have tried the samebut why column 1, column 2, column3 … are coming

[Brian_Mathew_Maben]

Hey @Ajinya_jorwekar ,

Have you used the workflow I have shared? because this issue does not arise when I run it.

Also try deleting the ‘Output Sheet’ and then rerun the process.

[Ajinya_jorwekar]

Thanks, I get the output as shown by you. But we have 4 rows only and in output we get 06 rows. why ?

[Ajinya_jorwekar]

we want in output only matching rows of reference column (Only those rows which have same value in Reference column i.e. INV NO. & GST Invoice Number)

[Brian_Mathew_Maben]

Hey @Ajinya_jorwekar ,

There are two entries with the same invoice number i.e. ‘002327000078’ in Sheet 1. Hence the output created two extra rows.

[Ajinya_jorwekar]

Yah, Thanks its working as expected. Really thanks for ypur valuable time.

[Brian_Mathew_Maben]

Hey @Ajinya_jorwekar,
Glad I could help, kindly mark the solution such that it will help the community.

[Vikas_M]

Hey @Ajinya_jorwekar ,

Can you please mark the right post as solution such that it would help the community to access the answer easily
Also it will motivate the users to answer and help others just like @Brian_Mathew_Maben did to you

Thank you in advance

[Ajinya_jorwekar]

Already marked in above conversation