Solved

Excel Lookup

Posted on 2009-05-07
14
828 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

864 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

16 Experts available now in Live!

Get 1:1 Help Now