Excel: how to populate an alphabetically-sorted column from contents of other columns?

B

Brigadier

Joined
14 Jan 2008
Messages
16,207
Reaction score
2,591
Location
Staffordshire
Country
United Kingdom
Hello all,


I have a number of columns on a spreadsheet (effectively, a table), each containing a range of text values.
(I believe / hope! that each text value is unique).

I want to create a single, separate column on this spreadsheet, which:

- takes all of the text values from the table, and
- puts them in alphabetical order, into this separate column.


All of the Google searches I've carried out only show how to sort a single, existing column, into alphabetical order.
Which, as the data I need to sort is across four or more columns, is useless to me.

Thanks in anticipation (y)
 
RandomGrinch said:
Can you "CONCATENATE" all the columns into one, and then alphabetically sort?
Click to expand...

I'm not sure how that would work.

Say I have four columns, containing the following values: A B C D

If I concatenate these, they'd become a single column (single cell) A, B, C, D , yes?

Doing the above for multiple rows, wouldn't sort the Ds for example, as it would sort by the first expression in each cell?

?
 
Is your table a fixed size? Does it always contain 4 columns and x number of rows with every cell in the table populated?

Do you need to do this with a formula which can be repeated every time the data in the table changes or is it just a one-off exercise?
 
Ok, sorry, I may have been overthinking what you wanted to do.
For something so simple, you would think there would be a command to stack columns, but I don't know of one.
As above, how many columns/rows do you have.

There are methods to stack columns, such as:

But they aren't exactly automatic.

I'm wondering if a roundabout, but very simple method, may be to save the file as a .CSV, find and replace commas with linefeeds, and then re-import!

Edit:
Just had a go in Libre Office and it works for a simple sheet.
Load .CSV into Writer, find and replace "," with "\n" (regular expression option ticked), and load it back into Calc.
A bit simpler than the YouTube video!
 
Last edited:
pcaouolte said:
Is your table a fixed size? Does it always contain 4 columns and x number of rows with every cell in the table populated?

Do you need to do this with a formula which can be repeated every time the data in the table changes or is it just a one-off exercise?
Click to expand...

Table subject to change, in both number of columns and rows.
(That said, when the table size changes, I could workaround by making and archiving a copy, and updating the new version).

No; not all cells populated.

Ideally, a change in the table - addition of data into a cell - would auto-update the alphabetically-sorted column.
 
RandomGrinch said:
Ok, sorry, I may have been overthinking what you wanted to do.
For something so simple, you would think there would be a command to stack columns, but I don't know of one.
As above, how many columns/rows do you have.

There are methods to stack columns, such as:

But they aren't exactly automatic.

I'm wondering if a roundabout, but very simple method, may be to save the file as a .CSV, find and replace commas with linefeeds, and then re-import!

Edit:
Just had a go in Libre Office and it works for a simple sheet.
Load .CSV into Writer, find and replace "," with "\n" (regular expression option ticked), and load it back into Calc.
A bit simpler than the YouTube video!
Click to expand...

I'll have a look over the next day or few; thanks!
 
The simplest route would perhaps be to use the string concat operator, &

Formula of
Code: 
= A1 & " - " & C1 & " : " & J1 " zzz " & D1

For A1, C1, J1, D1 values of 1, 2, 3, 4 that would give a text column of:

Code: 
1 - 2 : 3 zzz 4

Re-sorting them after a change would be a manual affair of doing an in place sort that does not expand to affect the other rows in the sheet.. If you change cells such that the sort order no longer is true another sort would be required


####

Unless you mean you want to perform a per-row sorting of some other values on the row, into a single cell, like this:

1678431231753.png


Where the values from columns a to d have been collected into f, in order (but each row requires a different sort order for the cells)

The formula for that above is a bit of a monster but easy to read with multiple lines:

Code: 
=CONCATENATE(
INDEX(A2:D2,1,MATCH(SMALL(COUNTIF(A2:D2,"<"&A2:D2),1),COUNTIF(A2:D2,"<"&A2:D2),0))," - ",
INDEX(A2:D2,1,MATCH(SMALL(COUNTIF(A2:D2,"<"&A2:D2),2),COUNTIF(A2:D2,"<"&A2:D2),0))," - ",
INDEX(A2:D2,1,MATCH(SMALL(COUNTIF(A2:D2,"<"&A2:D2),3),COUNTIF(A2:D2,"<"&A2:D2),0))," - ",
INDEX(A2:D2,1,MATCH(SMALL(COUNTIF(A2:D2,"<"&A2:D2),4),COUNTIF(A2:D2,"<"&A2:D2),0))
)
..and filled down


The 1,2,3,4 in the middle of each INDEX line is what determines twhich term is picked (in essence, sort the cells then pick the first, concat sort the cells, pick the second, concat...)

If your cells aren't in a range like A2:D2 I suggest you create some hidden set of cells somewhere that are that are contiguous and copy the values from your discrete cells. For example if you want to use columns A C E and J in your sorting, make column P a formula of =A1, column Q as =C1, R as =E1 and S as =J1.. This way Px:Rx is the range (paste the formula into notepad and do a find/replace A2:D2 with P2:R2 etc) - this stands a better chance of keeping the formula readable than trying to create a range out of the separate cells in the formula
 
Last edited:
I'm not clear what the task is. If the starting table is 4 columns x 4 rows:
q w e r
t u i o
p a s d
f g h j

Is the result meant to be 1 column with 4 rows
fghj
pasd
tuio
qwer

or 1 column with 16 rows:
a
d
e
f
g
h
i
j
o
p
q
r
s
t
u
w
 
pcaouolte said:
I'm not clear what the task is. If the starting table is 4 columns x 4 rows:
q w e r
t u i o
p a s d
f g h j

Is the result meant to be 1 column with 4 rows
fghj
pasd
tuio
qwer

or 1 column with 16 rows:
a
d
e
f
g
h
i
j
o
p
q
r
s
t
u
w
Click to expand...
From the description, I believe, 1 column, 16 rows - well that's what I've been working to! :)
 
RandomGrinch said:
From the description, I believe, 1 column, 16 rows - well that's what I've been working to! :)
Click to expand...
That's what I thought but some people seem to be concatenating 4 cells from each row into one cell, I can't see how that helps if the task is to sort every non-empty cell into a single column.
 
You must log in or register to reply here.
Back
Top