Comparing 2 columns of an excel with another excel sheet

Hi Team,

I am working on a problem wherein i have 2 excel sheets, the first one contains 2 columns - Order Number and P number which i need to compare with the 2nd excel sheet, and if some record which is there in the first excel and is not found in the 2nd excel needs to be highlighted red.

Capture
First sheet

Capture1
Second sheet

Hi. This would be the structure that you’d have to implement. Hope it helps. :slight_smile:

Read First Sheet - dt1
Read Second Sheet - dt2
For each row in dt1
           For each col in {Order number, P number}
                  If Not dt2.AsEnumerable.Any(Function(x) x(col).ToString.Equals(row(col).ToString))
                      Colour cell corresponding to row(col)

To colour the cell, you will first need to convert the row and col index to an excel cell: c# - How to convert a column number (e.g. 127) into an Excel column (e.g. AA) - Stack Overflow

Hi @alin.c.mihalea

I am relatively new to this technology, i am able to apprehend the code you have sent but i find it difficult to see how it would be done in UiPath, can you share a snap of the workflow.
Also i mostly work in python, so the is there a UiPath activity that i can use to color code the corresponding field?

Regards
Arjun

Hi. I did not create the actual workflow, just the structure above to guide you. But of course some minimal knowledge of UiPath is still required. To get started, I strongly encourage you to follow at least the Foundation course on https://academy.uipath.com/

The activity used for adding colour to a cell is called “Set Range Color”
image

Hi @shekhawat.arjun1307 ,

Just create a sample workflow.

Please do changes accordingly.
Test.xaml (14.2 KB)

Hi @ermanoj3101

This dosen’t seems to work for me.

Regards
Arjun

Hi @alin.c.mihalea

i can’t find an activity for the second for loop that you have implemented that is(There is an activity for each row but not for column) -
for each column{order number, P number}

Regards
Arjun

That would be a simple for each loop, that loops through an array which holds the column names.


Capture006

There seems to be an issue, can you help as to why i am getting this issue.

Hmm, I guess this is coming from the type of “item” variable. Should be of type string.
image

Another doubt i have is inside the if statement i will be using the set range color activity how should i mention both the column values(B and C) in the range properties of this activity.

Capture0010

Hi @ermanoj3101

The workflow you sent seems to run forever, as if it entered an infinite loop.
Can you check once and suggest what might be the issue?

Regards
Arjun

Hi @alin.c.mihalea

Is it possible for you to send an xml file.

Regards
Arjun

Hi @shekhawat.arjun1307

This can be done using the For Each Row in Data Table Activity, EAS and Set Range Color

Please find the attached xaml file

Comparing Two Columns of excel with another excel sheet.xaml (10.4 KB)

1 Like

Hi @kumar.varun2

Thanks, this works for me.

Regards
Arjun

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