Solved

Excel Lookup

Posted on 2009-05-07
14
817 Views
Last Modified: 2013-11-25
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????
0
Comment
Question by:j8547
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24323386
Should think so,

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

Chris
0
 
LVL 10

Accepted Solution

by:
Jon von der Heyden earned 500 total points
ID: 24323569
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24323604
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
 

Author Comment

by:j8547
ID: 24323610
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24323670
I don't think your attachment came through correctly - perhaps try again?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24323726
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
 

Author Comment

by:j8547
ID: 24323772
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24323790
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24323805
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
 
LVL 45

Expert Comment

by:patrickab
ID: 24324024
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24324058
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
 
LVL 45

Expert Comment

by:patrickab
ID: 24324106
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24324237
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
 
LVL 45

Expert Comment

by:patrickab
ID: 24324393
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now