How do I compare values from 2 excel workbooks and write the unique values in a new sheet?

Help StudioX
1

The files I am comparing have names in column A of both workbooks. I am having trouble with the advanced editor to get this done.

2

Hi @jenn.d

You have two sheets like Sheet1 and Sheet2. Then you want to compare Sheet1 values with Sheet2. which are unique in both sheets you want to write to the new sheet.

If my understanding is correct then follow the steps, here we are using LINQ Expressions,
→ Use the Read range workbook activity to read the Sheet1 and store in a datatable called dt1.
→ Use another Read Range workbook activity to read the Sheet2 and store in other datatable variable called dt2.
→ Then create another datatable variable called Output_dt.
→ Then use the below LINQ Expression in assign activity,

- Assign -> Output_dt = dt1.AsEnumerable.Where(Function(X) dt2.AsEnumerable.Any(Function(Y) X("ColumnA").Equals(Y("ColumnA")))).CopyToDataTable()

→ Use the Write Range workbook activity to write the Output_dt to the Sheet3 in your excel.

Hope it helps!!

1 Like
3

Hi @jenn.d ,

Detailed Steps

  1. Create Variables:
  • Create two DataTable variables: dtWorkbook1 and dtWorkbook2.
  • Create a Boolean variable to indicate if a match is found: matchFound.
  1. Read Data from Workbooks:
  • Use the Excel Application Scope to open the first workbook and the Read Range activity to read the data into dtWorkbook1.
  • Repeat the process for the second workbook and read the data into dtWorkbook2.
  1. Compare Data:
  • Use a For Each Row activity to iterate through dtWorkbook1.
  • Inside the loop, use another For Each Row activity to iterate through dtWorkbook2.
  • Compare the values in column A (assuming the first column is indexed as 0).
  1. Log Results:
  • Use an If activity to check if the names match.
  • Log the results using the Log Message activity or store the results in another DataTable or list for further processing.

Regards
Sandy