Sort/Reference formula help needed.
 

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?

Sort/Reference formula help needed.
 

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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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?

Sort/Reference formula help needed.
 

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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?

Sort/Reference formula help needed.
 

What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Sort/Reference formula help needed.
 

S7



"JBeaucaire" wrote:

What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Sort/Reference formula help needed.
 

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()-6COUNTIF($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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

S7



"JBeaucaire" wrote:

What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Sort/Reference formula help needed.
 

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.

"JBeaucaire" wrote:

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()-6COUNTIF($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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

S7



"JBeaucaire" wrote:

What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Sort/Reference formula help needed.
 

Fixed it! Thanks very much!

"Scott" wrote:

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.

"JBeaucaire" wrote:

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()-6COUNTIF($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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

S7



"JBeaucaire" wrote:

What row will the first formula be placed in? This is necessary to know to
make the array sequence properly.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Scott" wrote:

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.

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB