Transposing cells

Could you be more specific about "relevant rows". How do you determine relevance, it's not clear on your woorksheet?
OK, I see it now. You derived a list of unique values from column A. You wish to list all values from column B associated w each A value.

Paste this ARRAY FORMULA in B31:
=IFERROR(INDEX($B$5:$B$28,MATCH(0,COUNTIF($A31:A31,$B$5:$B$28)+($A$5:$A$28<>$A31),0)),"")

To confirm, press CTRL + SHIFT + ENTER. Curly brackets {} should embrace your formula, indicating an array formula.
Now use the drag handle to fill across a few rows, then drag down.

NOTE: if you can move the List elsewhere (say A31:?? becomes D5:??) and reserve Columns A and B for data, this form allows for data expansion.
=IFERROR(INDEX($B:$B,MATCH(0,COUNTIF($D5:D5,$B:$B)+($A:$A<>$D5),0)),"")XX ps This does not work-Lee XX

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

I forgot your file...

Originally Posted by StreekyD

.. It appears this formula is transposing all data from B and stringing it out...

Did you press CTRL + SHIFT + ENTER? The formula will not work otherwise. On my trials, it matched your example output exactly.

ps. OK, Expanding Alternate w entire columns doesn't work. Paste this ARRAY FORMULA in D5 and copy down:
=IFERROR(INDEX($A$5:$A$28,MATCH(0,COUNTIF($D$4:$D4,$A$5:$A$28),0)),"")
Now paste this ARRAY FORMULA in E5 and copy down:
=IFERROR(INDEX($B$5:$B$28,MATCH(0,COUNTIF($D5:D5,$B$5:$B$28)+($A$5:$A$28<>$D5),0)),"")

In C5, an array formula, copied across and down:
=IFERROR(INDEX($B:$B,SMALL(IF($A$5:$A$28=$A5,IF(COUNTIF($A$5:$A5,$A5)=1,ROW($B$5:$B$28))),COLUMNS($A:B))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Apols. i just noticed your expected answer!! It always helps to look.

D5, copied down:
=IFERROR(INDEX($A$5:$A$28,MATCH(0,INDEX(COUNTIF($D$4:$D4,$A$5:$A$28),0),0)),"")

E5, an array formula, copied across and down:
=IFERROR(INDEX($B:$B,SMALL(IF($A$5:$A$28=$D5,ROW($B$5:$B$28)),COLUMNS($A:A))),"")


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome. By the way, if this isn't just a one-time thing, you should probably define dynamic named ranges to use in these formulae:
In Name Manager - AppNumbers as formula =OFFSET(Sheet1!$A$1,4,0,COUNTA(Sheet1$A:$A)-4) would refer to A5:A??
Assuming you made your results range big enough, and there are no blanks in the defined area, this would automatically adjust to changing list size.
(NOTE: Offsetting from a cell OUTSIDE of the data (usually the header) keeps the reference intact even if the ENTIRE Data Range is deleted.)