Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed with cell reference | Excel Discussion (Misc queries) | |||
Lookup or reference function needed | Excel Worksheet Functions | |||
Address List Sort Help Needed - 1 attachment | Excel Discussion (Misc queries) | |||
Maintain Formula Reference (sort of) | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions |