Combined & Sorted Text Fields

A

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

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

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
 
H

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