Comparing Excel Sheets with Different Row Count

Help
, ,
1

Hi All,

I am just new to UiPath and I am in need on how this scenario will be automated.

I have 2 excel sheets. Sheet 1 will be the reference of checking. On the ID column of Sheet 1, it will compare all area codes (sheet2) that have the same ID number from sheet 1.

On column E (mismatch), the column name which has a mismatch from sheet 1 will be written and be separated by comma like in row 8 in sheet 2.

In addition, if cell is blank from sheet 1 and sheet 2, it will be considered as matched.

Thank you in advance for any help. It would be helpful if XAML is provided. :slight_smile:

This is the Sheet 1

image

This is Sheet 2

image

2

Is sheet 1 data is fixed?

3

Yes, should be. Because that will be the reference of validation

4

If yes then you can use them in a switch say
For each Row of sheet2
In switch it will check for ID
and accordingly it will go inside that case only
Then u can set validation say if Row(“Name”).Tostring.Trim=“John”
Then Row(“Mismatch”)=“” else Row(“Mismatch”)=“Name”
Again If for Country
At the end Write range

1 Like
5

Will it be okay if you provide an xaml? Sorry, I’m just new to UiPath :frowning:

Sorry, didn’t get your question earlier.

Sheet 1 is NOT fixed. It is dynamic. Sorry. Only the headers are fixed.

6

New Microsoft Excel Worksheet.xlsx (9.3 KB) Switch.xaml (31.1 KB)

1 Like
7

Appreciate your help.

The ID number in Sheet 1 has just one record and there is no “Reg” column here. While for sheet 2, there can be 10 or more “Reg” under the same ID number from Sheet 1. Actually, this is where I am having hard time creating.

These should how the sheets will look like:

Sheet 1
image

Sheet 2

image
image692Ă—495 16.1 KB

8

Hello Dear i havent make a sheet for first sheet instead i have created directly a switch cases
so dont refer sheet2 directly write range In Sheet1 I have written as Sheet2 so make change in script as sheet1 in write range

1 Like
9

What if values under Country column is dynamic? What could be the condition in the IF activity?

image

10

Is it in a variable or ???

11

Nope, it is not in the variable. I am not sure if I’m getting this right.

Should I change it as row(“Country”).ToString = “”?

12

Look If it is coming from sheet wise
as you said earlier do this
Read Range Sheet2 where u need to check and write Dt1
Read raneg Sheet1 which is a static data depending on which condition will be checked DT2

Use For Each Row for DT1
In that use
For each Row2 for DT2
If
Row(“ID”).Tostring=Row2(“ID”).TOSTRING
Then Check
If conditions for other say for name and country aacording assign Row(“Mismatch”)
and then use break.
at the end use write range

2 Likes
13

Is it too much if I request for updated xaml for this? Please?

14

Right now i am busy in my daily work
Try what i told earlier

1 Like
15

Okidokie, I will try to make it work.

Thanks a lot for your help. Sorry to disturb.