If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. 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 |
#4
|
|||
|
|||
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. 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. |
#5
|
|||
|
|||
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. 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
search button to report based on multiple combo boxs ( combo criteria based upon queries ) | [email protected] | Using Forms | 6 | October 5th, 2005 05:02 PM |
return a message to a text box based on a calculation | dtoney | New Users | 1 | February 8th, 2005 08:19 AM |
formula to return the value of a cell based on a looked up true reference | sarah | Worksheet Functions | 2 | February 2nd, 2005 08:15 PM |
Report Based Upon Parameter Query with Form References | Vincent DeLuca | Setting Up & Running Reports | 4 | July 19th, 2004 01:55 AM |
MATCH Function based on specified occurence (i,e return position based on 2nd occurence) | [email protected] | Worksheet Functions | 3 | December 10th, 2003 02:12 PM |