Drop Down List Question
 

I have a table containing Names and Class Days:

Joe Monday
Sue Tuesday
Bob Tuesday
Sam Monday
Bill Wednesday
Jon Thursday

I want to create a list to show Names per Class Day:
Monday Tuesday Wednesday Thursday
Joe Sue Bill Jon
Sam Bob

Drop Down List Question
 

Say input is in A2:B11. Say your output starts from F1 (Monday)
through L1 and employee names start at F2. Then at F2:
Simple version (produces #NUM! when no more employees)

=INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B
$2)+1),ROW()-ROW($F$1)))

Error checking (leaves cell blank if no more employees)
=IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()-
ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B
$11)-ROW($B$2)+1),ROW()-ROW($F$1))))

Either formula is an *array* formula. Commit with Shift+Ctrl+Enter.
You can copy the formula down and across.

HTH
Kostis Vezerides


On Apr 21, 7:41 pm, Dallas PM
wrote:
I have a table containing Names and Class Days:

Joe Monday
Sue Tuesday
Bob Tuesday
Sam Monday
Bill Wednesday
Jon Thursday

I want to create a list to show Names per Class Day:
Monday Tuesday Wednesday Thursday
Joe Sue Bill Jon
Sam Bob

Drop Down List Question
 

I can see the results when I "walk thru" the formula in the background, but
when I do SHIFT+CTRL+ENTER, no values are stored. I am running Excel 2003

"vezerid" wrote:

Say input is in A2:B11. Say your output starts from F1 (Monday)
through L1 and employee names start at F2. Then at F2:
Simple version (produces #NUM! when no more employees)

=INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B
$2)+1),ROW()-ROW($F$1)))

Error checking (leaves cell blank if no more employees)
=IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()-
ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B
$11)-ROW($B$2)+1),ROW()-ROW($F$1))))

Either formula is an *array* formula. Commit with Shift+Ctrl+Enter.
You can copy the formula down and across.

HTH
Kostis Vezerides


On Apr 21, 7:41 pm, Dallas PM
wrote:
I have a table containing Names and Class Days:

Joe Monday
Sue Tuesday
Bob Tuesday
Sam Monday
Bill Wednesday
Jon Thursday

I want to create a list to show Names per Class Day:
Monday Tuesday Wednesday Thursday
Joe Sue Bill Jon
Sam Bob

Drop Down List Question
 

I don;t understand this last post, what you mean by "walking through".
If your F2 (or whichever cell you chose) displays the formula, select
the cell, double-click to enter Edit mode, and then instead of Enter
use Shift+Ctrl+Enter.

Does this help?
Kostis

On Apr 21, 9:27 pm, Dallas PM
wrote:
I can see the results when I "walk thru" the formula in the background, but
when I do SHIFT+CTRL+ENTER, no values are stored. I am running Excel 2003

"vezerid" wrote:
Say input is in A2:B11. Say your output starts from F1 (Monday)
through L1 and employee names start at F2. Then at F2:
Simple version (produces #NUM! when no more employees)

=INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B
$2)+1),ROW()-ROW($F$1)))

Error checking (leaves cell blank if no more employees)
=IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()-
ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B
$11)-ROW($B$2)+1),ROW()-ROW($F$1))))

Either formula is an *array* formula. Commit with Shift+Ctrl+Enter.
You can copy the formula down and across.

HTH
Kostis Vezerides

On Apr 21, 7:41 pm, Dallas PM
wrote:
I have a table containing Names and Class Days:

Joe Monday
Sue Tuesday
Bob Tuesday
Sam Monday
Bill Wednesday
Jon Thursday

I want to create a list to show Names per Class Day:
Monday Tuesday Wednesday Thursday
Joe Sue Bill Jon
Sam Bob