|
Formula Help with Lists
I need to be able to pull a list from series of data without sorting or
filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
Hi!
Try this: Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7 ,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"") Copy down until you get blank cells. Biff "TN.Jim" wrote in message ... I need to be able to pull a list from series of data without sorting or filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
Thank You
That is perfect! "Biff" wrote: Hi! Try this: Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7 ,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"") Copy down until you get blank cells. Biff "TN.Jim" wrote in message ... I need to be able to pull a list from series of data without sorting or filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
You're welcome!
Biff "TN.Jim" wrote in message ... Thank You That is perfect! "Biff" wrote: Hi! Try this: Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7 ,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"") Copy down until you get blank cells. Biff "TN.Jim" wrote in message ... I need to be able to pull a list from series of data without sorting or filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
Hi,
There is another way to do this. Array entert the following formula (Ctrl+Shift+Enter) Enter Boy in cell A10 instead of cell A8. =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) Hope this helps "TN.Jim" wrote: I need to be able to pull a list from series of data without sorting or filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
Formula Help with Lists
Hello I have been trying to find help on filtering a list and have found this
posting which is great and helps however within my list I want it to look up two sections as follows: (IF(ROWS('Data extract table'!BM340:BM340)<=COUNTIF('Data extract table'!BM$3:BM$56000,1),INDEX('Data extract table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=1,(ROW('Data extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data extract table'!BM340))),IF(ROWS('Data extract table'!BM340:BM340)<=COUNTIF('Data extract table'!BM$3:BM$56000,2),INDEX('Data extract table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=2,(ROW('Data extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data extract table'!BM340)))))) It is providing me with the first list from patient type 1 but the patient type 2 list that I just wish to continue below it is coming up #num???? what am i doing wrong please? "Biff" wrote: You're welcome! Biff "TN.Jim" wrote in message ... Thank You That is perfect! "Biff" wrote: Hi! Try this: Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7 ,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"") Copy down until you get blank cells. Biff "TN.Jim" wrote in message ... I need to be able to pull a list from series of data without sorting or filtering... Example: A B 1 Jim Boy 2 Ann Girl 3 John Boy 4 Mike Boy 5 Tina Girl 6 Matt Boy 7 Beth Girl Then if a user selects "Boy" in cell A8, I want A9 = "Jim" A10 = "John" A11 = "Mike" A12 = "Matt" I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I get John in A10 etc? The actual list is much more complicated but I hope that this gives an idea of what I need to do. Thanks for the help! Jim |
| All times are GMT +1. The time now is 03:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com