| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reordering a List in Numerical Order
I have a list of names in cells A2:A16, and a list of numbers from 1 through
15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ....so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
for only 15 cells, i suggest u do it manually...be kind
happy holidays to all "bob" wrote: I have a list of names in cells A2:A16, and a list of numbers from 1 through 15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ...so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
I have thousands of cells but used only 15 in my example. i can't do it
manually. Bob "driller" wrote: for only 15 cells, i suggest u do it manually...be kind happy holidays to all "bob" wrote: I have a list of names in cells A2:A16, and a list of numbers from 1 through 15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ...so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
me too...
"bob" wrote: I have thousands of cells but used only 15 in my example. i can't do it manually. Bob "driller" wrote: for only 15 cells, i suggest u do it manually...be kind happy holidays to all "bob" wrote: I have a list of names in cells A2:A16, and a list of numbers from 1 through 15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ...so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Select the range you want to sort, or if the cells are all touching, select
only one cell (and it will automatically choose all of the cells). Under the data menu, choose 'Sort'. Sort by column B (or the column with the rank numbers) "bob" wrote: I have a list of names in cells A2:A16, and a list of numbers from 1 through 15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ...so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Oops, just saw the bottom half of that.
To see if I'm clear on this... column E is a list of multiple names from column A, and you're re-sorting them by their number? If that is the case... is there any pattern to what numbers go in each list? "~L" wrote: Select the range you want to sort, or if the cells are all touching, select only one cell (and it will automatically choose all of the cells). Under the data menu, choose 'Sort'. Sort by column B (or the column with the rank numbers) "bob" wrote: I have a list of names in cells A2:A16, and a list of numbers from 1 through 15 in cells B2:B16. In E2:E9, I have five names, each separated by a comma and a space. Each of the names in E appears in the list in A. In F2:F9, I want to reorder the names corresponding to the numbers in B, in ascending order. Example: Col A Col B Steve Blake 1 Mo Williams 2 Lynn Greer 3 Charlie Bell 4 Michael Redd 5 Chris McCray 6 David Noel 7 Bobby Simmons 8 Ruben Patterson 9 Ersan Ilyasova 10 Damir Markota 11 Charlie Villanueva 12 Brian Skinner 13 Dan Gadzuric 14 Andrew Bogut 15 Col. E Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut I want to take the each row in col E and reorder it as follows (first row shown here): Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut ...so that the names correspond to the numbers in B, lowest to highest: Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15. Any ideas on how to do this? thanks, Bob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Perhaps something along these lines might work here ..
Use Data Text to Cols (delimited) to split col E into cols E to I: Select the range E2:Exxxx, click Data Text to Columns, check "Delimited", click Next. In step 2, enter a comma in the "Other" box, then click Finish. Then place: In K2: =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)) Copy K2 to O2 In Q2: =INDEX($E2:$I2,MATCH(SMALL($K2:$O2,COLUMN(A:A)),$K 2:$O2,0)) Copy Q2 to U2 In W2: =TRIM(Q2&", "&R2&", "&S2&", "&T2&", "&U2) Then select K2:W2, copy down as far as required. The desired concat results with the names sorted left to right by the numbers in col B will be returned in col W. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bob" wrote: I have thousands of cells but used only 15 in my example. i can't do it manually. Bob |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Here's a quick sample construct to illustrate:
http://cjoint.com/?lClVndMR8Z Reordering a List in Numerical Order.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
fantastic. thank you very much.
Bob "Max" wrote: Here's a quick sample construct to illustrate: http://cjoint.com/?lClVndMR8Z Reordering a List in Numerical Order.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
You're welcome, Bob !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bob" wrote in message ... fantastic. thank you very much. Bob |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
I ran into a complication with one aspect of this solution that I hadn't
mentioned in the original post. For the formula =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)), I need to add an IF statement as follows: IF($D:$D="Mil.",[use E2],[use K2]. in your example, I inserted five columns K through O and added a second set of names. I changed the statement above to =INDEX($B:$B,IF($D:$D="Mil.",MATCH(TRIM(E2),$A:$A, MATCH(TRIM(K2),$A:$A,)))) ....but it returns a not available error on the second MATCH statement. Any idea of what i am doing wrong? Thanks, Bob "Max" wrote: Perhaps something along these lines might work here .. Use Data Text to Cols (delimited) to split col E into cols E to I: Select the range E2:Exxxx, click Data Text to Columns, check "Delimited", click Next. In step 2, enter a comma in the "Other" box, then click Finish. Then place: In K2: =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)) Copy K2 to O2 In Q2: =INDEX($E2:$I2,MATCH(SMALL($K2:$O2,COLUMN(A:A)),$K 2:$O2,0)) Copy Q2 to U2 In W2: =TRIM(Q2&", "&R2&", "&S2&", "&T2&", "&U2) Then select K2:W2, copy down as far as required. The desired concat results with the names sorted left to right by the numbers in col B will be returned in col W. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bob" wrote: I have thousands of cells but used only 15 in my example. i can't do it manually. Bob |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Max,
i ran into a complication with one aspect of this solution. i need to add an IF statement to the formula =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)). The IF statement is: IF $D$D="Mil."[use E2],[use K2]. In your excellent example, I inserted 5 columns K through O and modified the above formula to be: =INDEX($B:$B,IF($D:$D="Mil.",MATCH(TRIM(E2),$A:$A, MATCH(TRIM(K2),$A:$A,)))) ....but it returns a not-available error on the second MATCH statement. Any idea of what I may be doing wrong? Thanks, Bob "Max" wrote: Perhaps something along these lines might work here .. Use Data Text to Cols (delimited) to split col E into cols E to I: Select the range E2:Exxxx, click Data Text to Columns, check "Delimited", click Next. In step 2, enter a comma in the "Other" box, then click Finish. Then place: In K2: =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)) Copy K2 to O2 In Q2: =INDEX($E2:$I2,MATCH(SMALL($K2:$O2,COLUMN(A:A)),$K 2:$O2,0)) Copy Q2 to U2 In W2: =TRIM(Q2&", "&R2&", "&S2&", "&T2&", "&U2) Then select K2:W2, copy down as far as required. The desired concat results with the names sorted left to right by the numbers in col B will be returned in col W. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bob" wrote: I have thousands of cells but used only 15 in my example. i can't do it manually. Bob |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Bob,
Sorry, I seem to have missed this further response earlier .. If I've read your revised set-up and intent correctly, then this should work (revision is based on the earlier sample) .. In Q2: =IF(D2="Mil.",INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)) ,INDEX($B:$B,MATCH(TRIM(K2),$A:$A,0))) Copy Q2 to U2 In W2: =IF(D2="Mil.",INDEX($E2:$I2,MATCH(SMALL($Q2:$U2,CO LUMN(A:A)),$Q2:$U2,0)),INDEX($K2:$O2,MATCH(SMALL($ Q2:$U2,COLUMN(A:A)),$Q2:$U2,0))) Copy W2 to AA2 Then in AC2: =TRIM(W2&", "&X2&", "&Y2&", "&Z2&", "&AA2) Select Q2:AC2, copy down as far as required Col AC returns the required results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bob" wrote in message ... Max, i ran into a complication with one aspect of this solution. i need to add an IF statement to the formula =INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0)). The IF statement is: IF $D$D="Mil."[use E2],[use K2]. In your excellent example, I inserted 5 columns K through O and modified the above formula to be: =INDEX($B:$B,IF($D:$D="Mil.",MATCH(TRIM(E2),$A:$A, MATCH(TRIM(K2),$A:$A,)))) ...but it returns a not-available error on the second MATCH statement. Any idea of what I may be doing wrong? Thanks, Bob |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reordering a List in Numerical Order
Apologies. Think I forgot to fix the reference to col D earlier
The corrected formulas should read: In Q2: =IF($D2="Mil.",INDEX($B:$B,MATCH(TRIM(E2),$A:$A,0) ),INDEX($B:$B,MATCH(TRIM(K2),$A:$A,0))) Copy Q2 to U2 In W2: =IF($D2="Mil.",INDEX($E2:$I2,MATCH(SMALL($Q2:$U2,C OLUMN(A:A)),$Q2:$U2,0)),INDEX($K2:$O2,MATCH(SMALL( $Q2:$U2,COLUMN(A:A)),$Q2:$U2,0))) Copy W2 to AA2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop-Down List Choice Affects Multiple Cells? | Excel Worksheet Functions | |||
| Can I sort a list to match a previously existing order? | Excel Worksheet Functions | |||
| How to sort al list in random order for sampling? | Excel Discussion (Misc queries) | |||
| How do I list the most recent occrence of a purchase order? | Excel Worksheet Functions | |||
| list names in random order in Excel 2002 | Excel Discussion (Misc queries) | |||
Linear Mode
