Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •