# Thread: Using a lookup and retrieving a number of rows (XP)

1. ## Using a lookup and retrieving a number of rows (XP)

Dear Loungers,

I have a sheet with some refernce data, this is is data that is used for a variety of purposes in the spreadsheet, it is grouped and each group may have a number of items - which is unkown. The data is sourced from an access database and copied into 6the sheet. It would look something like this:

Group1 - Item1 - lots of data
Group1 - Item2 - lots of data
Group2 - Item3 - lots of data
Group3 - Item4 - lots of data
Group3 - Item5 - lots of data
Group3 - Item6 - lots of data

What I want to do is create a subset of the data in one of the sheets in the workbook, these are based on a selected Group and will be as many rows as there are items in the group populated with some data. If it was only one row I would use
=VLookup(SelectedGroup, DataSet, 3,FALSE)
Is it possible to use something similar to retrieve "n" items???

many thanks.................. liz

2. ## Re: Using a lookup and retrieving a number of rows (XP)

Does <post#=395235>post 395235</post#> have code to do what you want?

Additionally you could use autofilter and then copy the visible rows to another location or just work with it filtered. SUBTOTAL can give you stats on the visible data.

Steve

3. ## Re: Using a lookup and retrieving a number of rows (XP)

Steve,

Lovely!! the functions do what I need (for now!) so that is great. many thanks for your help and providing a simple and elegant solution.

liz

4. ## Re: Using a lookup and retrieving a number of rows (XP)

Steve,

One more thing... the functions are working well, what I do is have a formula which is in 40 rows and retrieves tha relevant data, 40 is the maximum number of possible matching rows. The formula is =VLIndex(SelectedStHA, OrganisationStatusDetails,2,ROW()). When there is nothing more to retreive the result is #NUM!. I want to suppress this so thought that I could use ERROR.TYPE. either I am doing it wrong or it doesn't work. I am trying this:
=IF(ERROR.TYPE(VLIndex(SelectedStHA,OrganisationSt atusDetails,2,ROW()))=6,"",VLIndex(SelectedStHA,Or ganisationStatusDetails,2,ROW()))

If there is nothing to retrieve it's fine otherwise instead of the expected result I get #N/A.

What am I doing wrong????

many thanks.......................... liz

5. ## Re: Using a lookup and retrieving a number of rows (XP)

Just use ISERROR:

=IF(iserror(VLIndex(SelectedStHA,OrganisationStatu sDetails,2,ROW())),"",VLIndex(SelectedStHA,Organis ationStatusDetails,2,ROW()))

Steve

6. ## Re: Using a lookup and retrieving a number of rows (XP)

Steve,

Oh yes, how silly!!!

And finally (you hope), when running the sheet the calculations are quite slow. The subset of data I am constructing is 40 rows by 6 columns that are based on a user selection from a list so it is quite a lot of calculating. Any suggestion show I could make it slicker , or even dispaly a warning whilst calculation is happening - it can take 10 minutes! Usually I use Names to save calculations to save space and make maintenance easier will this work in this case. It is also 1.8 mb which seems awfully large as there isn't heaps of data although there is the 240 calculations plus a few others. Any ideas for streamlining?

liz

7. ## Re: Using a lookup and retrieving a number of rows (XP)

The only other option is perhaps some kind of macro to extract what you want. User functions can be slow and if you have a lot of them (especially one that keeps checking the list, one at a time). Having a routine that goes thru the entire list once and extracts as desired might be better.

We could help with the coding, but you would have to expand on your setup and what you want the output to be like.

Steve

8. ## Re: Using a lookup and retrieving a number of rows (XP)

Steve,

That's very kind of you but I think that it may bring it's own problems as I will then have code I didn't cut and support may become an issue. So I will live with the slowness as it it only when they initially select the item required, this is only once on first using the spreadsheet and I will warn them that they need to go an make a cup of tea! I will try to get the size downas well although I'm not sure how yet.

Really grateful for you help................. liz

9. ## Re: Using a lookup and retrieving a number of rows (XP)

Another option might be just to use advanced filter to extract a list to a sheet from the source data based on a criteria.

Also, as I mentioned earlier, autofilter allows viewing of the subset of data without copying it. The SUBTOTAL function can extract various stats on this "visible data"

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•