return value based upon another

[dziw]

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
**** B
Jane C
Harry A
Sue C

What I then want to do is return the name of the nth 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.

[Aladin Akyurek]

=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
> **** 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]

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.

[Rowan]

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.
>
>

[Aladin Akyurek]

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.99999999999999E+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.
>
>