Sort/Reference formula help needed.

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Currently I have a data sheet that looks like this:

_____A_____B_____
1 A Data 1
2 B Data 2
3 A Data 3
4 C Data 4
5 B Data 5

I want to create a column that will sort and display and list all of the
"A's" in Column A, all of the "B's" in another column, all of the "C's" in
another column, and so on.

Basically I want it to read like this:
A B C
Data 1 Data 2 Data 4
Data 3 Data 5

How do I go about accomplishing this?
 
These are array formulas, you must enter them and press CTRL-SHIFT-ENTER to
confirm them. Curly braces { } will appear around your formula and the first
value will appear. If you forget and press a normal ENTER, you will get an
ERROR, press F2, then CTRL-SHIFT-ENTER to correct.

For "A":
=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="A",ROW($A$1:$A$5),""),ROW()))

....copy that cell down after the first value appears properly.

For "B" and "C" change the letter in the formula.
 
Thank you for your reply, however, I'm still experiencing "Formula contains
error" error messages.

What I want to do is to sort and display names in separate columns based
upon a ranking format of A,B,C,D. I want all of the A's in one column, all
of the B's in another, all of the C's in a third, and all of the D's in the
fourth.

Currently, in my data sheet, the A-D ranking is in columns L7:L150, with the
names in columns M7:M150.

Anymore insight on how to accomplish this? Thanks for everyone's help.
 
What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
 
These are still array formulas, enter with CTRL-SHIFT-ENTER, then copy down.
Also, I added some error checking so when the list runs out of matches you
will get blank cells instead of #NUM errors.

=IF(ROW()-6>COUNTIF($L$7:$L$150,"A"),"",
INDEX($M$7:$M$150,SMALL(IF($L$7:$L$150="A",
ROW($A$1:$A$143),""),ROW()-6)))

To start the "B" row in T7, change the "A" references to "B", etc.
 
that worked! Well, at least Columns A, B, and C. Column D is returning a
"#N/A" error on Column D only. Not sure why yet though.
 
Fixed it! Thanks very much!

Scott said:
that worked! Well, at least Columns A, B, and C. Column D is returning a
"#N/A" error on Column D only. Not sure why yet though.
Click to expand...
 
Back
Top