Compare Excel sheets and write data in new sheet

I have started working on Ui Path and below is the issue i have been facing.FilterTableE1.xaml (18.4 KB) I have two sheets where sheet1 haves the customers with invoice details and sheet2 having customers with the document numbers.

I have to check the customer in sheet2 with the sheet1 and identify whether sheet2 customer matches with sheet1 and then check if it has a document number, if yes then i have to copy the sheet1 data of that customer and as well sheet2 data for that particular cust and write it in new sheet with row wise.
Could you please give me an idea to achieve this.Compare Excel sheets.xlsx (12.2 KB)


You can read the two sheets into two different datatables and then use Join Datatables activity with Join type as Inner .

Specify the column Names in the Column Table 1 and Column Table 2 and condition and specify the output Datatable say OutDT2 .

once you have it in output Datatable , use filter Datatable to remove the rows with Document number is empty . (select remove , specify the output Datatable same as the input Datatable)

you can find the documentation below :slight_smile:

If the above solution is not feasible ,
2) Build A Master Datatable of columns containing DT1, DT2.

  1. read each sheet into data tables says DT1 and DT2.
  2. Iterate through DT1 using for each row
    2.1) put FilterWizard with Input Datatable as DT2 and specify output DT as tempDt2
    2.1.1) give condition to check if the customer is matching and document number is not empty
    2.2) put an if condition with tempDT.rows.count > 0
    2.2.1) then using add data row add it to the master data table which u created

Thank you, I will go through it and will reply for any information requires.

1 Like

I have tried but able to get only the first sheet data.
Please see the bot attached and excelCompare Excel sheets.xlsx (16.2 KB) FilterTableE1.xaml (16.3 KB)

1 Like

Hi Please check this file .:slight_smile:

FilterTableE1.xaml (22.2 KB)


thank you, but how to get the not macthed customer and it details in sheet1?

Got it by using Left join

1 Like

Great Vishwanth :slight_smile:

1 Like

one more question, I need to write if it is matched or not matched with a new column in the result sheet.

You can add one more column to the output Datatable using add data column ,
then use for each to iterate through the Output Datatable ,
then put the required condition and
use assign activity row(“Type”)= “matched” or row(“Type”)=Not matched
and then write into the excel :slight_smile:

1 Like

Got it. Thanks

Small correction, I’m getting duplicate values from sheet2. column: Document Number.

Expecting only 3 values

Hi Could you please check this.

HI Vishwanath ,

You can Use remove duplicate rows activity and provide the same datatable to input and output before writing it into excel . can you try this ? This will remove the Duplicate rows from the datatable

Yeah, ihave tried that, but due to the different invoice number , it is not deleting the duplicates.

I think this is becuase of left join. for each invoice number, it is generating a new row with document number.

Could you please check this Compare two sheets in excel and copy the data in Word and send an email

Hi vishwant ,

Refer this post to remove the duplicate row based on column

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