Improved matching

[RB Smissaert]

Perhaps this is old hat to many users of this group, but figured something
out that might be worth sharing.
Often I have to match up 2 ranges in a worksheet. Say in its simplest form
there is a range of numbers in column A with a header and a range of numbers
in column F with a header. Now I want to match up the numbers in column F
with the numbers in column A and put the matched numbers in column B. First
I used to do this with Lookup formula's, then I figured out that it was
somewhat faster with a Match formula and a sort, but now I found a much
quicker and more elegant way of doing this.
If the data is as above in columns A and F, starting in the first row (it
doesn't matter if there are doubles in either column) the following code
will do the matching up:

Sub MatchTest()

Dim LR1 As Long
Dim LR2 As Long
Dim i As Long
Dim n As Long
Dim c As Long
Dim TR()
Dim TR2()
Dim TR3()

'see where the last rows are
LR1 = Cells(65536, 1).End(xlUp).Row
LR2 = Cells(65536, 6).End(xlUp).Row

'sort list one
Range(Cells(1), Cells(LR1, 1)).Sort _
Key1:=Cells(2, 1), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom

'sort list two
Range(Cells(6), Cells(LR2, 6)).Sort _
Key1:=Cells(2, 6), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom

'set up the arrays to do the matching up with
TR = Range(Cells(2, 1), Cells(LR1, 1))
TR2 = Range(Cells(2, 2), Cells(LR1, 2))
TR3 = Range(Cells(2, 6), Cells(LR2, 6))

'do the matching up
For i = 1 To LR1 - 1
For n = c + 1 To LR2 - 1
If TR3(n, 1) > TR(i, 1) Then
Exit For
End If
If TR3(n, 1) = TR(i, 1) Then
TR2(i, 1) = TR3(n, 1)
Exit For
End If
c = c + 1
Next
Next

'put the matched up numbers in the sheet
Range(Cells(2, 2), Cells(LR1, 2)) = TR2

End Sub

RBS

[Tushar Mehta]

While I haven't timed the options you've considered, yes, there are
times when VBA code can be faster than XL functions. Apparently, this
is one of those times.

You can tweak your code even further. First, you might want to fill
the result array more compactly. Use a separate index RsltIdx and use
it as in
TR2(RsltIdx, 1) = TR3(n, 1): RsltIdx=RsltIdx+1

Second, if you drop the For loops and replace them with While loops,
you can speed up the code even more. A technique very useful in
processing long lists in 'batch mode.' The foll. is untested.

Idx1=2:Idx2=2:RsltIdx=2
While Idx1<= LR1-1 and Idx2<= LR2-1
if TR(Idx1,1)>TR3(Idx2,1) then
do
Idx2=Idx2+1
if Idx2>=LR2 then goto AllDone
loop while TR3(Idx2,1)< TR(Idx1,1)
elseif TR(idx1,1)<TR3(Idx2,1) then
do
Idx1=Idx1+1
if Idx1>=LR1 then goto AllDone
loop while TR1(Idx1,1)< TR3(Idx2,1)
else
TR2(RsltIdx,1)=TR(Idx1,1)
Idx1=Idx1+1:Idx2=Idx2+1:RsltIdx=RsltIdx+1
end if
wend
AllDone: Range(...)=TR2
end sub
--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

In article <OyZztYQuCHA.2808@TK2MSFTNGP12>,
bartsm...@blueyonder.co.uk says...

[RB Smissaert]

Thanks for the reply. I tried a while loop, but didn't find it any faster.
Tested it on 5000 row columns and both methods do it in about 120
milliseconds.
Your other suggestion of packing the array tight, that is not making any
empty elements, won't work as the numbers then won't match with the numbers
in the first column.
Please let me know if you can prove me wrong.

RBS

"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.188b85337...@msnews.microsoft.com...

[Myrna Larson]

Hi, Tushar:

I am interested in your comment re the speed of For/Next vs Do/Loop. Is this based on your
experience and testing, or examination of the ASM code?

How big is the difference? Why does it exist?

Myrna

>"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message

>> Second, if you drop the For loops and replace them with While loops,

[Tushar Mehta]

Hi Myrna,

Probably not much difference for a few thousand records. Maybe not for
even larger data sets given powerful modern machines and self-
optimizing compilers. Even less when the OP does not one but two sorts
before the matching loop!

But, the embedded 'For loops' code uses the outer loop and repeatedly
enters and exits the inner loop whenever TR3 > TR. Minor, but
something that was important on early-to-late '80s mainframes when
batch processing thousands of transaction records against millions of
master records (i.e., TR3 has significantly fewer records than TR).

And, it seemed that the OP was interested in a speed demon.

--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

In article <9k962voadehimoqed...@4ax.com>,
myrna...@charter.net says...