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

[robinbanks]

If the people you're writing to cannot understand the explanation of what you want, the fault isn't entirely theirs. Perhaps you could post an actual picture of what you have, and another picture of what you want?

 

[Brigadier]

If the people you're writing to cannot understand the explanation of what you want, the fault isn't entirely theirs. Perhaps you could post an actual picture of what you have, and another picture of what you want?

I agree.

Also doesn't help when posters ignore posts (#3), and persist with an approach already dismissed (concatenation).


A B C D
E F G H
I J K L
M O P
Q S T

to become

A
B
C
D
E
F
G
H
I
J
K
L
M
O
P
Q
S
T

Apologies if this formats badly.
I'm typing this on my phone (which is dying), on a train.

 

[Brigadier]

It did format badly.

The (missing) N and R were intended to be blank (cells)

 

[Brigadier]

It did format badly.

The (missing) N and R were intended to be blank (cells)

 

[robinbanks]

Post #3 made nearly no sense to me, so I didn't factor it into the spec, other than to lift from it that you had four columns

So you have A1 to E4 (20 cells in a 4x5), and you want G1 to G20 (20 cells in a 20x1):

Using a formula for this could be awkward, but here's the starting point that generated the above:

=INDEX($A$1:$E$4,ROUNDUP(ROWS($1:1)/COLUMNS($A$3:$E$4),0),MOD(ROWS($1:1)-1,COLUMNS($A$1:$E$4))+1)

It's not sorted; that could be a post op in another set of cells or more (bit torturous) formula. If your table is elsewhere, modify the three occurrences of $A1:$E4

Note: sorting the interim cells:

Also, if you don't want to have to modify 3 references to the table you can store reference to it somewhere in the sheet, e.g. put "A1:E4" in $G$1, and use the INDIRECT function (though be careful; you can dig a performance hole for yourself)

=INDEX(INDIRECT($G$1),ROUNDUP(ROWS($1:1)/COLUMNS(INDIRECT($G$1)),0),MOD(ROWS($1:1)-1,COLUMNS(INDIRECT($G$1)))+1)

I added another row to the table and changed the G1 value to A1:E5 to change the extents of the table the H column formula looks at