How to compare 2 excel columns in 2 excel workbooks with huge number of rows

Question

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 21-Mar-18 19:05pm

planetz

Updated 22-Mar-18 14:11pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Patrice T · Accepted Answer · 2018-03-22T14:12:00

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.