| Home |
| Search |
| Today's Posts |
|
#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 |
| 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 | |||
Threaded Mode
