return value based upon another
 

Ok, I have an Excel question... here's the scenario:
I've got 2 columns, we'll call Names and Classes...
Names: Classes:
Bob A
Dick B
Jane C
Harry A
Sue C

What I then want to do is return the name of the -n-th occurrence of
class -x-.
For example, I want the 2nd occurance of class "C", which would return:
"Sue".
Is there a way to do this?

Hope I was clear enough in what I'm trying to do. :)


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=470591

=INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=D2,ROW($B$2:$B $6)-ROW($B$2)+1),E2))

where D2 houses a class of interest like C and E2 N like 2.

The formula must be confirmed with control+shift+enter.

dziw wrote:
Ok, I have an Excel question... here's the scenario:
I've got 2 columns, we'll call Names and Classes...
Names: Classes:
Bob A
Dick B
Jane C
Harry A
Sue C

What I then want to do is return the name of the -n-th occurrence of
class -x-.
For example, I want the 2nd occurance of class "C", which would return:
"Sue".
Is there a way to do this?

Hope I was clear enough in what I'm trying to do. :)

Awesome! I really appreciate the help!

I've got one more thing I was trying to do, and I was hoping I'd be
able to figure out how to do it from your code, but no such luck. :p

Ok, let's say I have a column: Thar Be Letters Here,
A,B,C,A,A,C,D,E,G,G,F,H,D

What I want to do is capture what letters appeared in what order, but
ONLY the first occurence of that letter (kinda like a top 10 thing
going on). So, I want to output:

Another Column,A,B,C,D,E,G,F,H

Any suggestions?

Thanks in advance.


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=470591

Assuming your data is in A1:A30 then in B2 (B1 must be empty) enter:
=IF(OR(COUNTIF($B$1:B1,A1:$A$30)=0),INDEX(A1:$A$30 ,MATCH(0,COUNTIF($B$1:B1,A1:$A$30),0)),"")
This is an array formula committed with Ctrl+Shift+Enter.
Copy down as far as neccessary.

Hope this helps
Rowan

dziw wrote:
Awesome! I really appreciate the help!

I've got one more thing I was trying to do, and I was hoping I'd be
able to figure out how to do it from your code, but no such luck. :p

Ok, let's say I have a column: Thar Be Letters Here,
A,B,C,A,A,C,D,E,G,G,F,H,D

What I want to do is capture what letters appeared in what order, but
ONLY the first occurence of that letter (kinda like a top 10 thing
going on). So, I want to output:

Another Column,A,B,C,D,E,G,F,H

Any suggestions?

Thanks in advance.

You're welcome.

Taking up your additional question, it looks like you want to create a
list of distinct items.

Let A3:A15 house the letters sample you provided.

In B1 enter: 0

which is required.

In B2 enter the label: Idx

In B3 enter & copy down:

=IF(A3<"",IF(ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.9 9999999999999E+307,$B$1:B2)+1,""),"")

In D1 enter:

=LOOKUP(9.99999999999999E+307,B3:B15)

In D3 enter & copy down:

=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,$B$3:$B$15,$A$3:$A$15),"")

The desired list will appear in D from D3 on.

The foregoing, I'd like to add, is a pretty fast formula system.

dziw wrote:
Awesome! I really appreciate the help!

I've got one more thing I was trying to do, and I was hoping I'd be
able to figure out how to do it from your code, but no such luck. :p

Ok, let's say I have a column: Thar Be Letters Here,
A,B,C,A,A,C,D,E,G,G,F,H,D

What I want to do is capture what letters appeared in what order, but
ONLY the first occurence of that letter (kinda like a top 10 thing
going on). So, I want to output:

Another Column,A,B,C,D,E,G,F,H

Any suggestions?

Thanks in advance.