A good algorithm for this specific problem

[dull1234]

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

[Poweruser]

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)

[ADezii]

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.

Dim rng_1 As Range
Dim rng_2 As Range
Dim rngRef_1 As Range
Dim rngRef_2 As Range
  
  
Set rng_1 = Worksheets("Sheet1").Range("$A$2:$F$1000")
Set rng_2 = Worksheets("Sheet2").Range("$A$2:$F$1000")
  
For Each rngRef_1 In rng_1
  For Each rngRef_2 In rng_2
    If rngRef_1.Value <> "" Then
      If rngRef_1.Value = rngRef_2.Value Then
        Debug.Print "Value " & rngRef_1.Value & " in Sheet1 found at " & _
                     rngRef_2.Address & " in Sheet2"
      End If
    End If
  Next
Next