How to compare 2 identical columns from different sheets in excel?

[sshussa]

Please help me for the requirements:

1. Compare identical columns. For e.g. if Column A from Sheet1 and Column B Sheet2 matches then do the following
   • append as “matched” in the Cell Value in sheet2.

[lakshman (Ganta lakshman)]

@sshussa

Welcome to our uipath community.

There are many posts releated to this in forum. Could you please look into it one by one.

https://forum.uipath.com/search?q=Compare%20two%20datatables

[sshussa]

Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

The above link is close what I am looking for…but there are some below changes to it…

1. Should read data from sheets in excel rather than datatables
2. When matched should add a status as ‘matched’ in the separate column with out overriding to the existing sheet.

Appreciate your help and support on the same.

[samir (Samir Mohite)]

Hi @sshussa,

I’ll suggest you to follow the steps below.

1. Read those columns using 2 Read Range activities —> sh1 & sh2 (output variables)
2. Use Add Data Column activity to add 1 column (“Result”) in 2nd datatable —> to write result. (Matched or not)
3. Use 2 For Each Row, one inside other as shown below.

create output index variable for both For Each Row —> ind1 & ind2

Two For Each Row —> to iterate through datatables. (but 1st for each row will iterate for every item in sheet2 like Cross product Cartesian for that we can use those indexes)
these index variable are to assure index of both of the iteration. So if iteration index is same then it’ll check for the condition.

Condition row1(0).ToString = row2(0).ToString —> will check for the data.
two assigns used accordingly to write result. (As shown in screenshot 1)

4. Use write range ouside of those 2 for each row, with addHeaders property checked.

That’s it

[sshussa]

Thanks a lot, Samir.

What if I need to send an email with the records data only for those matched results in the end?
Here is my example:
Sheet1 | Sheet2
Col ID | Col ID, Col Name, Col Email, Col Amount, Col Results (this is the status col for match or not).

Condition: if Col ID from sheet1 matches with Col ID from Sheet2 then do as the following:

• Dynamically append matched or not in the sheet2 - Col Results without overiding the existing sheets2 values.
• Finally, send an email with the record or row details from sheet2 for the matched column.

Also, Would you please kindly share an example with the code.Thank you very much.

Warm Regards.

[anwar_sd]

hai samir could you attach the xaml file

[sshussa]

Any one would share the xaml file that would be super helpful.

[samir (Samir Mohite)]

@anwar_sd

hai samir could you attach the xaml file

hi @anwar_sd,

@sshussa

What if I need to send an email with the records data only for those matched results in the end?

@sshussa, after adding findings (Matched Or not) in next column, using Filter Data Table activity, we can get row with matched result. And then using for each row on that filtered dataTable you can mail those one by one.

here I’m attaching example for your better understanding,
—> Compare2dataColumns.zip (21.2 KB)

Just have a look in excel data of sheet1 & sheet2 and run it.

sheet1 —> sheet2 —>
Output sheet2 with filtered data in message box (Matched)

—>

[sshussa]

Thanks, Samir,
I am getting this error : Activity could not be loaded because of errors in XAML.

[sshussa]

Thanks Samir for your help and support on the same