By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

A good algorithm for this specific problem

P: 5
Hi all
I am having a question about Excel VBA
Hope that this is the right place to ask

Now i am having 2 giant spreadsheets, one has about 2000 entries, another one has about 1000 entries. The 1000 entries one contain information that i want to match it with the 2000 entries one

So, i came up with the code that for every entry in the 2000-entry file, i check with every entry in the 1000-entry file, see if there is a match

but then , the running time will be: 1000^2000 times, which is too much and not efficient

I know it is possible to use Vlookup function, but what if there is duplicate value in the 1000 entries and i only need the maximum number (assume that the information i need is numeric) ?

if you can provide a way to use Vlookup, that would be great
and a way / algorithm for VBA would be awesome. And a small pieces of the sample code would be great.

I was thinking about that, as soon as you find the entry in 1000-entry file, take the maximum value, and for the next run, just ignore all the duplicate of the last one. so the 1000-entry will no longer be 1000 entries after each run but less.

assume that we are working with different worksheet in the same workbook
thank you very much
Feb 23 '10 #1
Share this Question
Share on Google+
2 Replies


P: 12
For every record in A (1000), check every record in B (2000) => 1000 * 2000 not 1000^2000

if the check is something like A.attr1 = B.attr2 then this is exactly what a SQL-JOIN does (very fast)
Feb 23 '10 #2

ADezii
Expert 5K+
P: 8,638
Here is a code snippet that I wrote for a User that compares every Value in Sheet1 within the Range $A$2:$F$1000 to every Value in Sheet2 for the same Range. This is a very CPU intensive process, since it involves approximately 36,000,000 iterations of the Nested Loops (approximately 2 minutes). This should be a good start.
Expand|Select|Wrap|Line Numbers
  1. Dim rng_1 As Range
  2. Dim rng_2 As Range
  3. Dim rngRef_1 As Range
  4. Dim rngRef_2 As Range
  5.  
  6.  
  7. Set rng_1 = Worksheets("Sheet1").Range("$A$2:$F$1000")
  8. Set rng_2 = Worksheets("Sheet2").Range("$A$2:$F$1000")
  9.  
  10. For Each rngRef_1 In rng_1
  11.   For Each rngRef_2 In rng_2
  12.     If rngRef_1.Value <> "" Then
  13.       If rngRef_1.Value = rngRef_2.Value Then
  14.         Debug.Print "Value " & rngRef_1.Value & " in Sheet1 found at " & _
  15.                      rngRef_2.Address & " in Sheet2"
  16.       End If
  17.     End If
  18.   Next
  19. Next
Feb 24 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.