Combined & Sorted Text Fields

[andyholland]

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?

[bigwheel]

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

[Gord Dibben]

andy

Select the rows and Data>Sort>Options>Sort left to right>OK>Ascending.

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
<[email protected]> 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?

[Herbert Seidenberg]

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 Insert>Name>Define
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(VARY4,12)
and copy down. Do not mix upper and lower case letters in DARRY.