Read an Excel sheet and compare values

Help
1

Hi,
I need some help with Excel automation please. I have 2 excel files (Sheet 1 and Sheet 2). Both Sheets have values in Column A (ID number) and Column B (Store Number) that I need to compare to values in Column A (ID number) and Column B (Store Number) for Sheet 2 and if those values already exist together in Sheet 2 (For example, ID number and Store Number value in Sheet 1 Row 1 matches ID number and Store Number value in sheet 2), then do nothing, else, assign the Values in Sheet 1 and Sheet 2 to unique variables. All values are strings. I got so far -

Open Excel Application Scope for Sheet 1, Read range.
For each row,
Assign Col 1 row 1 value to Var1, Col 2 row 1 to Var2
Open Excel Application Scope for Sheet 2, Read range.
Assign Col 1 row 1 value to Var3, Col 2 row 1 to Var4
Assign StringA=Var1+Var2 (for example if Var1 is 551 and Var2 is 001, this creates a unique string
551001)
Assign StringB=Var3+Var4
If, StringA=StringB, do nothing
else, create assign StringA to FinalStr

The problem is, for every row in Sheet1, I want it to go through each row in Sheet 2 and see if StrA exists and if not, assign StringA to FinalStr. How can I do this?

THANKS IN ADVANCE

2

@jaspreet1003,

Use nested Loop one For Each Row inside other For each row and then compare both sheet values

1 Like
3

Use workbook activity to read excel file

Store 1 excel in dt1 and use add column activity to add new datacolumn in dt1, this new datacolumn is your (col1,row1) + (col2,row1)

Follow same for sheet2,

And compare new column with below code to get matched(string A =string B)

Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)

And to get non matched item use below code.

Get Non-matched Items.xaml (9.7 KB)

Thank you

1 Like
4

Thanks!

5

THANKS a lot Rahatadi!!! This does it.

1 Like
6

@jaspreet1003
Welcome…

1 Like
7

Thanks!!! great to be here…

8

@jaspreet1003

Please close thread by marking useful reply as a answer :blush:

9

Hi all,

I have somewhat similar query, I need to compare values from excel with the values present on a website so as to select the proper option from excel, can you please tell me how should I do it.