Compare column A from 2 different sheets of same workbook. For matched cell, copy data from column B & Column C from sheet2 to column B & C of sheet1 and for unmatched cells, copy those rows to a different sheet

Help Activities
, ,
1

So, there is a workbook with sheet1, sheet2, sheet3
Compare each cell one by one from column A of sheet2 to all values of column A in sheet1.
If matched, copy column B&C value from sheet2 and paste it in column B&C of sheet1
If unmatched copy the whole row from sheet2 and paste it in sheet 3.

Attaching the excel for more clear vision
Note: Sheet1: MTAXReport, Sheet2: WFR Report, Sheet3: Not in WFR
Applied Fors - Billing Workbook Template - Copy.xlsx (3.2 MB)

2

Hi @ayushi_jain3 ,
We have 2 ways
1.use activity
read 2 sheet to get data table
for each data table 2 inside data table 1
use if condition
if row(“your A column”).toString = line(“your A column”).toString
assign row(“your B column”) = line(“your B column”).toString
and row (“your C column”) = line(“your C column”).toString
else
add line to data3

→ after loop, write range data1 again to sheet 1, write range data3 to sheet 3

way2. we can use linq
regards,

3

Hey @Nguyen_Van_Luong1 , the for each method is taing too long to executre since there are around 5000 rows in datatable. I also tried linq query but it is not getting exceuted. Attaching the xaml for both ways that I am trying.

Linq query is disabled in same xaml
Sequence1.xaml (17.4 KB)

4

Hello @ayushi_jain3

  1. Open Excel File: Use the “Excel Application Scope” activity to open your Excel file.
  2. Read Data:
  • Read data from Sheet1 and Sheet2 into two DataTables.
  1. Comparison and Update:
  • Use a “For Each Row” loop to iterate through rows in Sheet1.
  • Check if the value in the current row’s column A exists in Sheet2.
  • If a match is found, copy values from Sheet2 to Sheet1.
  1. Create DataTable for Unmatched Rows:
  • Create an empty DataTable to store unmatched rows.
  1. Comparison and Copy Unmatched Rows:
  • Use another “For Each Row” loop to iterate through rows in Sheet2.
  • Check if the value in the current row’s column A does not exist in Sheet1.
  • If no match is found, add the entire row to the DataTable for unmatched rows.
  1. Write Data Back to Excel:
  • Write the updated DataTable for Sheet1 back to Sheet1.
  • Write the DataTable for unmatched rows to Sheet3.

Thanks & Cheers!!!

5

this method is taking too long to execute
since there are around 5000 rows to iterate

6

@ayushi_jain3

  1. Minimize the number of activities inside loops.
  2. Use Excel’s built-in functions where possible.
  3. Consider parallel processing techniques.
  4. Optimize read and write operations to Excel (batch processing).
  5. Efficiently use DataTable operations (e.g., LINQ queries).
  6. Be mindful of memory usage.
  7. Adjust UiPath settings for parallel execution.
  8. Use UiPath’s profiling tools to identify bottlenecks.
  9. Ensure sufficient hardware resources.