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

Brigadier · 8 Mar 2023

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

RandomGrinch · 8 Mar 2023

Can you "CONCATENATE" all the columns into one, and then alphabetically sort?

Brigadier · 8 Mar 2023

RandomGrinch said:
Can you "CONCATENATE" all the columns into one, and then alphabetically sort?

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?

?

pcaouolte · 8 Mar 2023

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?

RandomGrinch · 8 Mar 2023

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!

Brigadier · 9 Mar 2023

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?

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.

Brigadier · 9 Mar 2023

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!

I'll have a look over the next day or few; thanks!

RandomGrinch · 10 Mar 2023

Here's some VBA, that may help?

How to stack columns of data into one column in Excel - nandeshwar.info

This post will show you how to stack columns in Excel. This function will help you to bring data from various columns into one column.

nandeshwar.info

robinbanks · 10 Mar 2023

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:

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

pcaouolte · 10 Mar 2023

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

RandomGrinch · 10 Mar 2023

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

From the description, I believe, 1 column, 16 rows - well that's what I've been working to!

pcaouolte · 10 Mar 2023

RandomGrinch said:
From the description, I believe, 1 column, 16 rows - well that's what I've been working to!

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.

robinbanks · 10 Mar 2023

pcaouolte said:
some people seem to be concatenating 4 cells from each row

Tends to happen when the person giving the spec says:

Brigadier said:
Say I have four columns

Brigadier · 10 Mar 2023

Required result as described above:

1 Column
X rows

Brigadier · 10 Mar 2023

robinbanks said:
Tends to happen when the person giving the spec says

I have no idea how anyone thought concatenation was desired, requested, or was of any help, without wishing to sound rude.

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

Similar threads