Remove unmatching rows between 2 excel

Hey everyone,

I have 2 excel docs and 1 of the excel is the main excel . They both consist of different kind of headers and columns, but 1 column is same lets call that column “number”

what i want to do is I want to check between those “number” values and delete the ones that they dont both have. So to conclude I want to delete the rows from the main file which numbers didnt match.

so for example:
main excel
number | name | from
23---------eric------US
1----------susan-----PL
4----------john-------UK
99---------paul------GR

2nd Excel
number | mail | age
12--------yahoo–34
99--------google–25
11--------outlook–45

so after the execution main excel should be like this:

number | name | from
99---------paul------GR

1 Like

Hi @berkaykor,
If number can be used as only relation you can just simply use Read Range on both excels and using For Each Row extract data and compare with any element of other excel. If they match you can write row to new file if not then go to next row etc.

Try searching on Forum as here we have a lot of topics about how to compare excel data.

the problem is the main excel consists of +1500 rows so for each is a long way to do it in my opinion. is there a way to do it in a single execution?

I don’t think so. Another way is to have it in database but each operation on data where you need to compare something requires to go through the loops.

Well i tried with a loop but at somepoint around 700/800th row it crashes. And I checked again there are 4000 rows :smiley: so using loops wouldnt be safe and efficient. it takes around 2 seconds for each row so 4000x2= around 135 minute

Well to be honest excel isn’t the perfect tool to keep huge amount of data as it takes a lot of RAM during operation on it. Better approach here would be to have database where you can just make operation (compare) using simple queries. This would be more efficient. Eventually if that’s not an option you should define such macro in excel and work on data in Studio after macro will do it’s job.

Using a database is not an option to be honest. I thought about using VBA for this job, would I encounter errors on writing and reading to xlsm type files via uipath?

I was rather thinking about making macro in excel and make results showed up in separate sheet from which you could grab final data with Studio - if that’s make sense in your case.

There are couple more jobs in this process, and i will schedule it with orchestrator. So picking up final version from another sheet is okay but i need to execute the macro with uipath.

It should work as well. You can test it using Execute Macro activity from official UiPath.Excel.Activites package.