concatenate cells in alphabetical order

[Dr Paul]

How can I concatenate several cells into a new cell, but in alphabetical order?

PART 1
Example:

Say I have a list of artists in cells A1 to E1

For instance

A1 = "Queen"
B1 = "Santana"
C1 = "Kraftwerk"
D1 = "Oasis"
E1 = "Barry Manilow"

Can I make a formula that fills cell F1 with the five artists from A1:E1, but in alphabetical order?

ie the succesful formula would give an output to cell F1 of "Barry Manilow, Kraftwerk, Oasis, Queen, Santana"

----------------------

PART 2

Same problem but this time I want the the five values to appear in alphabetical order in five new cells, from F1:J1

So we would get:

F1 = Barry Manilow
G1 = Kraftwerk
H1 = Oasis
I1 = Queen
J1 = Santana

[DonkeyOte]

PART1 is far harder to achieve than PART2 .... both would realistically warrant use of helper cell... eg

A2: =COUNTIF($A1:A1,A1)+COUNTIF($A$1:$E$1,"<"&A1)
copied across to E2
(the first COUNTIF is to handle duplicity should it arise)

You can then use the output in Row 2 to determine output of F1:J1, ie

F1: =INDEX($A$1:$E$1,MATCH(COLUMNS($F1:F1),$A$2:$E$2))
copied across to J1
(PART1 would then be a concatenation of the F1:J1)

If you want to avoid helpers and/or achieve PART1 without PART2 then I would say you should be looking to use VBA as native functions aren't great at dealing with String Arrays.

[Dr Paul]

Thanks DonkeyOte

I don't understand INDEX, MATCH or even COUNTIF, so I just copied and pasted and this hasn't worked for me this time.

I can see how it nearly works because the first formula has given numbers to the five names in correct order but then the output from F1:J1 looks like this:

#N/A, Kraftwerk, Oasis, Barry Manilow, Barry Manilow

I probably need to go and study these functions a bit.

Attached...

[DonkeyOte]

Error all mine... typo in my original post ... should read:

F1: =INDEX($A$1:$E$1,MATCH(COLUMNS($F1:F1),$A$2:$E$2,0))
copied across