VLOOKUP - need more than 65,000 lines 1

[mudstuffin]

I am trying to use VLOOKUP to return data from a different sheet. However, the data I need to look through takes up more than the 65,000 lines in in one sheet. Can I put part of the data in one sheet, and the rest in another (or maybe another two columns in same sheet) and then use the VLOOKUP to search through both and return the match. The data to look through is only 2 columns (5 digit code, then description), but there is about 100,000 of them.

Thanks in anticipation.

 

[xlbo]

Ouch - personally, I'd use Access - a vlookup on 100000 rows is gonna take a while - specially if you have more than 1 or 2 of them but, if you really wanna do it in excel, try this
Based on 2 sets of data on the same sheet. Use 2 rangenames - Data1 and Data2
=IF(NOT(ISNA(VLOOKUP(A1,data1,2,FALSE))),VLOOKUP(A1,data1,2,FALSE),IF(ISNA(VLOOKUP(A1,data2,2,FALSE)),"No Match",VLOOKUP(A1,data2,2,FALSE)))
Rgds
~Geoff~

 

[DaleWatson123321]

Hi mudstuffin,

VLOOKUP might be one possibility.

Another, perhaps better option is to utilize Excel's database functions.

You could, for example, have a VBA routine that would extract the data from two databases located on two separate sheets. This extracted data would then be combined on another sheet.

If you would like to go this route, I can help with a VBA example, but preferably I should get you to send me a file that would include:

a) A brief example of the type of data you're working with.

b) An example of the type of data you need to extract from the databases - i.e. is it one record or more than one type of record that needs to be extracted.

If you can email me right away, I'll see if I can get a response (working example) back to you ASAP, as I'm leaving for vacation shortly.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca

 

[mudstuffin]

Thanks Geoff & Dale for your replies.

I think it will be best if I use the VLOOKUP, but am willing to give another possbile solution a try. It does however need to be done in Excel, as the users only have access to this. I have tried the VLOOKUP on about 50,000 lines, and it doesnt struggle at all with that many. It is only searching through two columns. What I need to be able to do, is type in the relevant number in one sheet, the VLOOKUP searches through the columns in another sheet/s, and then pulls the data from the next column. This way, I can hide the sheet/s from the user, so it is neater, and can't be tampered with easily. The data is fairly simple, and is for company group numbers and their names i.e.

Group No Group Name

00001 McDonalds
00002 Burger King
00003 Kentucky Fried Chicken
00004 Taco Bell

So when the user inputs 00003 into the sheet, the next cell automatically inputs Kentucky Fried Chicken in it. The sheet with all the data in it is nicely hidden & out of the users concern. Sorry if I should have explained this a bit more previously.

n.b. I did attempt this through VBA (courtesy of Skip I think), but this ran very slowly, which is why I have now gone down the VLOOKUP route, which seems unfazed by the large numbers.

Geoff, can your example be tweaked to pull the data from a seperate sheet/s, and Dale, I would like to give the VBA idea a go. Have I supplied enough info for you to have a bash....?

Many thanks for your help.


Mudstuffin
themudstuffin@yahoo.com

 

[xlbo]

Yeh - it just uses range names so you can create the names on 1 sheet or on seperate sheets - the formula will still work

On the subject of VLOOKUP not being fazed with large numbers.....I'm afraid you're wrong. It'll be fine if you don't have too many vlookup formulae but once you have a coupla hundred vlookup formule with the size of data you're dealing with, the re-calc time will be loooooooooooooooooooooooooooooong Rgds
~Geoff~

 

[danvlas]

As far as I know, an Excel sheet is limited to 65536 rows.
Aren't you going over that limit?
Or am I just mistaken?

Dan

 

[xlbo]

Dan - hence the need for the vlookup to work on 2 seperate sheets.... Rgds
~Geoff~

 

[mudstuffin]

Thanks Dan. You're not mistaken. This is the problem I am trying to get around. Thanks anyway.

 

[DaleWatson123321]

Mudstuffin,

The VBA option I spoke of might have been a reasonable option, but given the specifics, I have to READILY admit that the VLOOKUP option is the BEST way to go in your situation.

And "way to go" Geoff. I would suggest that Geoff's formula should work.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca

 

[mudstuffin]

Geoff,

I've now tried your original suggestion:-

=IF(NOT(ISNA(VLOOKUP(A1,data1,2,FALSE))),VLOOKUP(A1,data1,2,FALSE),IF(ISNA(VLOOKUP(A1,data2,2,FALSE)),"No Match",VLOOKUP(A1,data2,2,FALSE)))

...and it works great. You were correct in that the re-calc time would be slow. It was really slow on 500 vlookups, but when I reduced it to 400, it made a massive difference & runs ok.

Thanks for your help and I've given a a 'star' in appreciation.

Mudstuffin.

 

[xlbo]

Thanx for the star - Slight append to the vlookup info - If you can get the data sorted in order, you can lose the 4th argument (FALSE) and re-calc should be faster still - I always use false 'cos it only looks for EXACT matches but for big sets of vlookups, if you can sort in order, it can make a big difference Rgds
~Geoff~

 

[mudstuffin]

Thanks again Geoff.

I generally tend to sort the data, as I understood that this had to be done for the VLOOKUP to work. I normally also use the false argument to ensure an exact match.

So does that mean that if I sort it, take out the FALSE, it will still give an exact match....?

Thanks.


mudstuffin.

 

[xlbo]

Actually, having had time to think about it, because you are checking 2 lists, if it doesn't find the item in the 1st list, it may go for a near match instead and the formula needs it to throw an error if there is not an exact match found in the 1st column. You can take out the false in the last 2 vlookups but not the 1st 2. Soooooo, a slightly amended (and possibly slightly quicker recalc) would be:
=IF(NOT(ISNA(VLOOKUP(A1,data1,2,FALSE))),VLOOKUP(A1,data1,2,FALSE),VLOOKUP(A1,data2,2))

This seems to still give the correct answer BUT will NOT throw an error if an exact match isn't found in data2 - it'll give the nearest match
Rgds
~Geoff~

 

[mudstuffin]

Thanks again Geoff.

I gave this one a go. It seems to work ok, but if there isnt a match, it leaves #N/A in the field, and I need to keep it blank until something is typed in the correct cell, otherwise I'll have 400 rows of #N/A. Your first one works well as I changed the "no match" part to "" which keeps it blank when nothing is input, and it runs ok with 400 rows.

Do you have any suggestions for my other question relating to copying just the input sheet to another workbook, which I raised yesterday....? Sorting this one out will just abput put the icing on the cake...!!

That is until I think of something else to improve it with...

Many Thanks again for your time Geoff.


mudstuffin.

 

[xlbo]

try this'n then:
=if(A1="","",IF(NOT(ISNA(VLOOKUP(A1,data1,2,FALSE))),VLOOKUP(A1,data1,2,FALSE),VLOOKUP(A1,data2,2)))
ps - I've replied to your other thread as well
Rgds
~Geoff~