Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel lookups (XP)

    I tried searching for this, but no luck...

    Here goes:
    I'm trying to find the Nth occurrence of an item, rather than the first only. I can do it ok if all items are sorted, but not if they're not.
    For example:
    <pre>Jones Eggs
    Jones Bacon
    Jones Toast
    Smith Toast
    Smith Ham
    </pre>

    This works fine for me as such. I can get the count of the Jones records or the Smith records, etc. I can also pull out each column B value based on knowing which column A item I'm looking for (If, that is, they are sorted by column A). But, if I add another "Jones" line at the end, I can't get to it's value without first sorting by column A, which I don't want to do given the particular worksheet I'm on. If I could find the Nth occurrence of "Jones", that would work perfectly.

    I'm trying to accomplish this WITHOUT using vba, with a formula only. Any ideas?
    Thanks,

  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: Excel lookups (XP)

    Assume your "table" is in Cols A and B, starting in row 1.

    In D1 enter in the value for the occurence you are interested in
    In E1 enter the name you are interested in
    In C1 enter the formula:
    <pre>=COUNTIF($A$1:A1,$E$1)</pre>

    and copy C1 down (autofill) thru the rows covered in A/B

    In F1 enter:
    <pre>=INDEX(B:B,MATCH(D1,C:C,0))</pre>

    which is the result in Col B corresponding to the "d1"th occurence of E1 in Column A.

    Steve

  3. #3
    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: Excel lookups (XP)

    Here is a way if you do not want the intermediate column calculated (that is no formula in Col C)
    D1 and E1 are still the occurence number and the name in Col a respectively
    In a cell add the ARRAY formula (confirm with ctrl-shift-enter):
    <pre>=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$E$1, ROW($A$1:$A$100)),$D$1))</pre>


    Expand the range as desired

    Steve

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups (XP)

    Thanks so much for the quick answer! I used the first example, with some changes of course (the real problem is never quite as simple as the sample used to describe the problem...). I used Offset instead of Index, only because it was easier, and the range to search wasn't always known.

    One final question: Is there any way to conditionally set the height of rows, without VBA? Since I allocated a set number of rows to each section whether they were all needed or not, It would be great if I could shrink the blank rows on sections which had fewer data items.

    Thanks again,

  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: Excel lookups (XP)

    Manually or VB are the only means of changing the height. It can not be done via formulas

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups (XP)

    On second thought, this method (using SMALL) is MUCH better, requiring fewer (and shorter) formulas. Mostly I used the INDEX function after all, as it seems simpler, with fewer arguments, but sometimes had to use the OFFSET function, because I needed to return a range of cells. My raw data looks like:
    <pre>Person1 Project1 DataItem DataItem ....
    Person2 Project2 DataItem DataItem ....
    Person3 Project1 DataItem DataItem ....
    etc.
    etc.
    </pre>

    I added a list of people and a list of projects on another sheet for reference, then created a presentation sheet with the formulas we've been discussing. The result ends up looking like:
    <pre>Person1
    Project1 DataItem DataItem ...
    Project2 DataItem DataItem ...
    etc.
    etc.
    Person2
    Project1 DataItem DataItem ...
    Project2 DataItem DataItem ...
    etc.
    etc.

    etc.
    </pre>

    The only problem is I had to allocate a certain number of rows for each person, so some people have more blank space below their project details than others. That's why I asked about conditionally shrinking or hiding rows.

    With slight modifications, I should be able to make another sheet which is Project-centric rather than Person-centric, but using the same raw data area.
    Thanks again for all your help!

  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: Excel lookups (XP)

    Could you use a pivot table to get results that you want?

    Or perhaps just a macro routine to "create" your output rather than formulas

    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
  •