Reordering a List in Numerical Order

B

bob

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
 
L

~L

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)
 
L

~L

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?
 
M

Max

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)),$K2:$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.
 
M

Max

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,COLUMN(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
 
M

Max

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,COLUMN(A:A)),$Q2:$U2,0)),INDEX($K2:$O2,MATCH(SMALL($Q2:$U2,COLUMN(A:A)),$Q2:$U2,0)))
Copy W2 to AA2
 
Top