Results in Subsequent Columns

[sglick]

Hi --
Is it possible to add information in a column next to a unique list, and if the unique list is found several times to have those results returned in subsequent columns? For example:

Column A: Dept. Name

Bio
Bio
Bus
Bus
Eng
Eng

Column B: Instructor Name

John
Sue
Don
Don
John
Jake

Column C: Unique List (Full Time Faculty)

John
Sue
Jake

I would like the results:
John Bio Eng
Sue Bio
Jake Eng

Any advice would be greatly appreciated. Thanks!

[rylo]

HI

If you data is in the range A2:B7 with headings in A1:B1, and your list of unique names is in the range C2:C4 then in D2 array enter (ctrl, shift enter) the formula

=IF(COLUMN()-3>COUNTIF($B$2:$B$7,$C2),"",INDEX($A$1:$A$7,SMALL(IF($B$2:$B$7=$C2,ROW($B$2:$B$7),""),COLUMN()-3)))

Copy down / across as required.


HTH

rylo

[sglick]

Thanks. It worked a couple of times, but I mainly received #NUM and #Value returns. If you think of anything else, please let me know. Stephanie

HI

If you data is in the range A2:B7 with headings in A1:B1, and your list of unique names is in the range C2:C4 then in D2 array enter (ctrl, shift enter) the formula

=IF(COLUMN()-3>COUNTIF($B$2:$B$7,$C2),"",INDEX($A$1:$A$7,SMALL(IF($B$2:$B$7=$C2,ROW($B$2:$B$7),""),COLUMN()-3)))

Copy down / across as required.


HTH

rylo

[Teethless mama]

Hi --
Is it possible to add information in a column next to a unique list, and if the unique list is found several times to have those results returned in subsequent columns? For example:

Column A: Dept. Name

Bio
Bio
Bus
Bus
Eng
Eng

Column B: Instructor Name

John
Sue
Don
Don
John
Jake

Column C: Unique List (Full Time Faculty)

John
Sue
Jake

I would like the results:
John Bio Eng
Sue Bio
Jake Eng

Any advice would be greatly appreciated. Thanks!

Try this:

=IF(ISERR(SMALL(IF($B$2:$B$7=$C2,ROW(INDIRECT("1:"&ROWS($B$2:$B$7)))),COLUMNS($A:A))),"",INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7=$C2,ROW(INDIRECT("1:"&ROWS($B$2:$B$7)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
copy across and down

[starguy]

Thanks. It worked a couple of times, but I mainly received #NUM and #Value returns. If you think of anything else, please let me know. Stephanie

see the attach file for a different approach.

[rylo]

Hi

Did you array enter the formula using the CTRL SHIFT and enter keys?


rylo

[sglick]

I CTR-Shift-entered and I received this error. I think the problem is when I modify the rows (in columns A & B there are 330 entries):

=IF(ISERR(SMALL(IF($B$2:$B$330=$C2,ROW(INDIRECT("1:" &ROWS($B$2:$B$330)))),COLUMNS($A:A))),"",INDEX($A$2:$A$330,SMALL(IF($B$2:$B$330=$C2,ROW(INDIRECT("1:"&ROWS ($B$2:$B$330)))),COLUMNS($A:A))))

What did I do wrong? Thanks. Stephanie

Hi

Did you array enter the formula using the CTRL SHIFT and enter keys?


rylo

[vane0326]

Try...


Input formula in cell D2 and copy down.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$2:$B$8,MATCH(0,IF($B$2:$B$8<>"",COUNTIF($D$1:D1,$B$2:$B$8))),0)))


Input formula in cell E2 copy across and copy down.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,IF(MATCH($A$2:$A$8&$B$2:$B$8,$A$2:$A$8&$B$2:$B$8,0)=ROW($A$2:$A$8)-ROW($A$2)+1,ROW($A$2:$A$8)-ROW($A$2)+1)),COLUMNS($E2:E2)))))


Remember both formulas are arrays need to hold down:

Ctrl,Shift,Enter.

Hope it helps!

[sglick]

Thank you very, very much

Try...


Input formula in cell D2 and copy down.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$2:$B$8,MATCH(0,IF($B$2:$B$8<>"",COUNTIF($D$1:D1,$B$2:$B$8))),0)))


Input formula in cell E2 copy across and copy down.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,IF(MATCH($A$2:$A$8&$B$2:$B$8,$A$2:$A$8&$B$2:$B$8,0)=ROW($A$2:$A$8)-ROW($A$2)+1,ROW($A$2:$A$8)-ROW($A$2)+1)),COLUMNS($E2:E2)))))


Remember both formulas are arrays need to hold down:

Ctrl,Shift,Enter.

Hope it helps!