corresponding data

[Jack Sons]

Hi all,

In sheet A in col AU are more than 600 numers, say 344, 2368 etc. In col BI
of sheet A are corresponding amounts (numbers or nothing, no text, no
formulas). Sheet B in col. M also has some numbers in it (numbers or
nothing, no text, no formulas) which all occur in col AU of sheet A.

I need efficient (fast) code to copy the corresponding amount in col BI of
sheet A to col N in sheet B, say from row 2 (in sheet B) to row 60.

The result (the amount in col N of sheet B) should be like this:

sheet A sheet B

col AU col BI col M col N

row 345 2368 240 row 44 2368 240

I use XP SP2 and Excel 2K. Thanks in advance for your assistance.

Jack Sons
The Netherlands

 

[vezerid]

If the key numbers in SheetA!AU:AU are not repeated, then you need
VLOOKUP().

In SheetB!N2:
=VLOOKUP(M2, SheetA!AU:BI, 15, false)

HTH
Kostis Vezerides

 

[Jack Sons]

Kostis,

I'll try to be more clear.

The numbers in col M of sheet B are also somewhere in col AU of sheet A. In
the same row as the number in col AU of sheet A, but in col BI, is a number
that I need in col N in sheet B, in the same row as the number that is the
basis for this all. I know it could be done with vlookup, but in this case I
can't use a worksheet function. Not even by letting VBA put it in cells of
col N of sheet B.

So VBA has to do the work of vlookup. What is fast code to do that job?

Jack.

 

[vezerid]

Jack,
Of course you can always use formula and then Copy|Paste Special... |
Values, to eliminate any trace of formulas. Still, this is a search
problem where the source data is not ordered and as such it would
require rather elaborate data structures and algorithms to solve in
less than O(m*n), where m is the number of rows in SheetA and n is the
number of rows in your target sheet. VLOOKUP is itself efficiently
written. So the best I can come up with is using VLOOKUP from VBA.

Sub MyVlookup()
dim sh1 as worksheet, sh2 as worksheet
Set sh1 = thisworkbook.sheets("SheetA")
Set sh2 = thisworkbook.sheets("SheetB")

i=2
while sh2.cells(i,13)<>""
sh2.cells(i,14) =
application.worksheetfunction.vlookup(cells(i,13),
sh2.range("AU:BI"),15,false)
i=i+1
wend
end sub

HTH
Kostis