Combined & Sorted Text Fields
 

I have 12 columns of Letters that need to be combined into one cell in
the correct alphabetical order. Eg. Column 1 may have C whilst column 2
has B and so on. I need the last column to sort the letters
alphabetically so it would be simply BC in the last cell.

Any ideas?


--
andyholland
------------------------------------------------------------------------
andyholland's Profile: http://www.excelforum.com/member.php...o&userid=26491
View this thread: http://www.excelforum.com/showthread...hreadid=397574

You could select the range containing your data and the sort alphabetically.
Then add each cell (=A1&B1&C1 etc) to get your last column entry. Sorting is
normally done by column but if use choose the Options button, you can change
the orientaion of sorting from "top to bottom" to "left to right"

"andyholland" wrote:


I have 12 columns of Letters that need to be combined into one cell in
the correct alphabetical order. Eg. Column 1 may have C whilst column 2
has B and so on. I need the last column to sort the letters
alphabetically so it would be simply BC in the last cell.

Any ideas?


--
andyholland
------------------------------------------------------------------------
andyholland's Profile: http://www.excelforum.com/member.php...o&userid=26491
View this thread: http://www.excelforum.com/showthread...hreadid=397574

andy

Select the rows and DataSortOptionsSort left to rightOKAscending.

In row 1 column 13 enter =A1&B1&C1&D1 etc.

Drag/copy that formula down column 13.


Gord Dibben Excel MVP

On Sun, 21 Aug 2005 09:38:39 -0500, andyholland
wrote:


I have 12 columns of Letters that need to be combined into one cell in
the correct alphabetical order. Eg. Column 1 may have C whilst column 2
has B and so on. I need the last column to sort the letters
alphabetically so it would be simply BC in the last cell.

Any ideas?

If you don't want to sort each row separately and want the results to
be dynamic, do this:
Name your data array DARRY.Use InsertNameDefine
Also define the following names:
VARY2 = CODE(INDEX(DARRY,ROWD,))
VARY3 = SMALL(VARY2,VARY5)
VARY4 = CHAR(VARY3)
VARY5 = {1,2,3,4,5,6,7,8,9,10,11,12}
ROWD = ROW()-ROW(DARRY)+1
In your last column enter
=INDEX(VARY4,1)&INDEX(VARY4,2)&..........&INDEX(VA RY4,12)
and copy down. Do not mix upper and lower case letters in DARRY.