Comapring multiple coulmns in Excel Files

Hi All,

I have two Excel files
Excel1:
image

Excel2:
image

I need to compare first two columns in both the sheets and if matched, update the third column in first sheet taking value from second sheet 3rd column. Also I want to validate if a cell in 1st sheet is empty then write a exception.

Please help me friends. I have gone through many topics but can’t find the exact solution.

1 Like

Hi
Welcome to uipath community
hope these steps would help you resolve this
–use a excel application scope and pass the file path of first excel as input
–insde the scope use a read range activity and get the output with a variable of type datatable named dt1
–use anothe excel application scope and pass the file path of second excel file as input
and same insde that scope use a read range activity and get the output with a variable of type datatable named dt2
–now use a for each row actvity and pass the variable dt1 as input, change the variable name from row to row1
–inside this loop use another for each row activity and mention the input as dt2 and change the variable name from row to row2
–inside this inner loop use a IF condition with expression like this
row1(0).ToString.Equals(row2(0).ToString) AND row1(1).ToString.Equals(row2(1).ToString)

if true it will go to THEN part where we can use a assign activity like this
row1(2) = row2(2).ToString

and use a BREAK activity next to this assign activity to avoid looping even after finding the match

if the above condition falls it will go to ELSE part where another if condition lke this
String.IsNullOrEmpty(row1(1).tostring)
if true it will go to THEN part of that inner if condition which means that cell is empty in that row, and where we can use a THROW activity with any business exception we want like this
New BusinessRuleException(“your exception message”)

in the above expression we used a conditon which will validate this
Not String.IsNullOrEmpty(row1(1).tostring)

where 1 in row1(1) is the column index and we can mention any column index we want and it usually starts from 0 for the first column

hope this would help you
Cheers @Ven

1 Like

Thanks @Palaniyappan for quick response!
I have tried with for each row earlier but I observed it will only work if data is at the same row in both excels.
So thought of using lookup table activity.
Anyway, I will give it another try using for each.

yah for each row will check each row one by one so does in both the datatable with each row in sequence

sure
Cheers @Ven

Paste following formula in cell D2 of Excel1 and highlight all rows under column D corresponding to data and click on menu Home->Fill->Down, you will get desired values from Excel2 in column D of Excel1. Update column C with the values populated in column D and then delete column D.

=VLOOKUP(A2,[Excel2.xlsx]Sheet1!A$2:C$5,3,FALSE)

Update C$5 in this formula according to number of rows in Excel2.

1 Like

@Palaniyappan Thought For each wont work. Its working fine for now.
One more question…
I have columns names as below. Problem here is column names contains line breaks So tool unable to detect the column name. How to remove this line breaks?
Ex:
image
image

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