Click here to Skip to main content
14,767,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have two excel workbooks each of which has huge amount of rows (in lakhs).
I need to check how many E column rows of book 1 are matching with B column rows of book 2.

I have tried vlookup, index-match, match - but all in vain! These operations are taking forever to execute. And then I need to pastespecial the values.

Since I am not mapping any value, just matching columns and counting similar values, is there any alternate and effective way that does not take hours?

Please help!!

What I have tried:

I have tried vlookup, index-match, only match.
I have found a solution that suggests using partial match with 2 vlookups but that does not help either.
I have tried conditional formatting as well in which case the filtering is taking a lot of time.
Posted
Updated 22-Mar-18 15:11pm
v2

1 solution

Quote:
I have two excel workbooks each of which has huge amount of rows (in lakhs).

With such a large number of rows, anything matters, and excel may not be the best tool for this problem.
Excel is very nice, but it is at expense of speed. Database like SQL will handle you data in a more efficient fashion.
Sorting the data have a huge impact on performance.
Quote:
I have tried vlookup, index-match, only match.
I have found a solution that suggests using partial match with 2 vlookups but that does not help either.
I have tried conditional formatting as well in which case the filtering is taking a lot of time.

The exact formulas you used and the data matters. Not every formula work with every data, and some combinations are more efficient than others.

Without details on what you have done, it is impossible to give you better advices than those generalities. And teaching you databases is out of scope of this forum.
   
Comments
planetz 29-Mar-18 2:28am
   
Hi, I took your advice to shift the data to database. Now it is much more easy and fast. Thank you..!!
Patrice T 29-Mar-18 4:32am
   
You are welcome

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900