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

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

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

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

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

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

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

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

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

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

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

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

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

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