Excel lookups (XP)

Topic

New Reply

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:

Jones    Eggs
Jones    Bacon
Jones    Toast
Smith    Toast
Smith    Ham

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,

Reply | Quote

Replies

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:

=COUNTIF($A$1:A1,$E$1)

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

In F1 enter:

=INDEX(B:B,MATCH(D1,C:C,0))

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

Steve

Reply | Quote

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):

=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$E$1,ROW($A$1:$A$100)),$D$1))

Expand the range as desired

Steve

Reply | Quote

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,

Reply | Quote

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

Steve

Reply | Quote

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

Steve

Reply | Quote

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,

Reply | Quote

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:

Person1   Project1   DataItem   DataItem   ....
Person2   Project2   DataItem   DataItem   ....
Person3   Project1   DataItem   DataItem   ....
etc.
etc.

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:

Person1
   Project1   DataItem   DataItem   ...
   Project2   DataItem   DataItem   ...
   etc.
   etc.
Person2
   Project1   DataItem   DataItem   ...
   Project2   DataItem   DataItem   ...
   etc.
   etc.

etc.

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

Person1   Project1   DataItem   DataItem   ....
Person2   Project2   DataItem   DataItem   ....
Person3   Project1   DataItem   DataItem   ....
etc.
etc.

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:

Person1
   Project1   DataItem   DataItem   ...
   Project2   DataItem   DataItem   ...
   etc.
   etc.
Person2
   Project1   DataItem   DataItem   ...
   Project2   DataItem   DataItem   ...
   etc.
   etc.

etc.

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!

Reply | Quote

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):

=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$E$1,ROW($A$1:$A$100)),$D$1))

Expand the range as desired

Steve

Reply | Quote

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:

=COUNTIF($A$1:A1,$E$1)

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

In F1 enter:

=INDEX(B:B,MATCH(D1,C:C,0))

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

Steve

Reply | Quote