Excel Lookup

I have a workbook with 2 excel sheets. In the first one i have an id and name column. My code runs through each row in the id column and looks up the id in a table on the other worksheet and retrieves/pastes the name int he name column and movbes onto the next row. There are only about 10 different id's but i ahev doing a VLookup for every row and there are 500 rows and increasing at the moment. Is the a better way to do this where i can look up say id 1, retrieve its name and then paste the answer for all the id 1's and so on????
j8547Asked:
Who is Participating?
 
Jon von der HeydenCourse Leader & Managing DirectorCommented:
You shouldn't need to loop thru the range.  You can run a vlookup on the entire range.  See example below, and attachment example is also provided.
Sub MyLookup()
 
Dim lRow As Long
 
lRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
 
With Sheets("Sheet1")
    .Range("B2:B" & lRow) = Application.WorksheetFunction.VLookup(.Range("A2:A" & lRow), Sheets("Sheet2").Range("A:B"), 2, 0)
End With
 
End Sub

Open in new window

Example.xls
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Should think so,

Best is if you can provide a sample workbook we can work with

Chris
0
 
RichardSchollarCommented:
Yes, you don't need to loop - you can do it all at once.  I presume you have a good reason for wanting to do this via code rather than a worksheet formula.  The following will apply the names to the entire list in Sheet1:

Richard
Sun ApplyNames()
Dim rngID As Range, rngLookup As Range
 
'assume Sheet1 holds the 500 IDs and blank name column
'assume Sheet2 range A2:B11 holds your unique IDs and the Names you want to apply to Sheet1
 
With Sheets("Sheet1")
  Set rngID = .Range("A2:A" & .Cells(.Rows.Count,"A").End(xlUp).Row)   'assume header in row 1
End With
 
Set rngLookup = Sheets("Sheet2").Range("A2:B11")   'amend as appropriate
 
'now apply names using Vlookup function of the WorksheetFunction object:
 
rngID.Offset(0,1).Value = Application.Vlookup(rngID,rngLookup,2,False)
 
End Sub

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
j8547Author Commented:
This is a quick summary of the code. I have added 2 excel sheets and if u run the code you will see what happens. IT is quick but when you have over 1000 fields it's not as quick.
0
 
RichardSchollarCommented:
I don't think your attachment came through correctly - perhaps try again?
0
 
patrickabCommented:
j8547,
I'll bet there is no practical difference in time between looping through the range and applying a worksheet formula to the whole range. For less than 5000 rows the time to run both macros has got to be short.
Here on EE there appears to be a feeling that looping through a range is not such a good idea. Having said that I am not quite sure what those critics think the compiler does when other solutions are offered. The compiler has to work through the range in the same way. It would be interesting for those 'looping-through-the-range' critics to run some tests to prove their point.
Patrick
0
 
j8547Author Commented:
Works perfectly. Would it be any quicker if by some way you used arrays. someone suggested that to me but i din't know exactly how and if it would be any quicker.
0
 
RichardSchollarCommented:
No, I don't think you will get any improvement in the given situation.  Depending on the precise make up of your file and what formulas and/or event code you might have within it, you may get a performce increase by temporarily turning off automatic calculation and temporarily disabling events.

Richard
0
 
Rory ArchibaldCommented:
Patrick,
Do you see a difference between these two? (I sure do!) :)
Rory

Sub test1()
   Dim n As Long
   For n = 1 To Range("A1:AB5000").Count
      Range("A1:AB5000").Cells(n).Value = 1
   Next n
End Sub
Sub test2()
   Range("A1:AB5000").Value = 1
End Sub

Open in new window

0
 
patrickabCommented:
Rory,
Nice illustration but is it relevant to this sort of question where one is only looping through one column of data? I actually did say "For less than 5000 rows the time to run both macros has got to be short." and you have illustrated that very well with what is in effect a 140,000 row example.
Change your first macro to that shown below. Sure there's a difference but it is not significant in real life terms.
I accept that your code is more elegant but I can't see it really matters in most practical applications. Do please persuade me otherwise.
Patrick
 

Sub test1()
   Dim n As Long
   With Sheets("Sheet1")
        For n = 1 To 5000
             .Cells(n, 1) = 1
        Next n
   End With
End Sub

Open in new window

0
 
RichardSchollarCommented:
Hi Patric

Try running that when you have other formulas in the workbook pointing at the cells you are modifying eg Sumproducts and Vlookups.

Richard
0
 
patrickabCommented:
Richard,
I have worked with data sets 25,000 rows long and about 40 columns wide with VLOOKUPs and SUMPRODUCTs aplenty and the processing time has never been a problem. I have no argument with the theory but in practise I wonder how often it really matters.
Patrick
0
 
Rory ArchibaldCommented:
Patrick,
Based on your last evaluation of my VLOOKUP example, I suspect you are running a Cray and nothing is significant! ;)
I was mostly addressing your comment:
"Here on EE there appears to be a feeling that looping through a range is not such a good idea. Having said that I am not quite sure what those critics think the compiler does when other solutions are offered. The compiler has to work through the range in the same way. It would be interesting for those 'looping-through-the-range' critics to run some tests to prove their point."
If you really want an argument against that theory, try the two below (and note the row numbers for the second one). Even changing the first one to only do 500 rows is slower on my machine than the 50000 rows for the second.
Rory

Sub test1()
   Dim n As Long
   For n = 1 To Range("A1:AB1000").Count
      Range("A1:AB1000").Cells(n) = "=5+2"
   Next n
End Sub
Sub test2()
   Range("A1:AB50000") = "=5+1"
End Sub

Open in new window

0
 
patrickabCommented:
Rory,
Thank you for that illustration. I take your point.
That got me to wondering why I had not seen any difference. Your comment about my Cray got me wondering if it made any difference. It dawned on me that all my work on reasonably large data sets was on machines working as thin clients with who knows what doing the work - the limitation was the network - not processing power.
Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.