Firstly, apologies if this has been asked before but I have been trawling the site for half an hour and haven't found a solution to the question I'm asking. Some of the questions are close but I haven't been able to apply the answers to this issue.

In a nutshell, I have two columns of data in a spreadsheet, column A are the "groups" and column B is a list of names. I would like to transpose the data, grouping by column A, as follows.

CURRENT LAYOUT

C-- Steve
A-- James
A-- Alex
B-- Sarah
D-- David
A-- Joanna
D-- Colin
A-- Brian
D-- Peter
E-- Sally

DESIRED LAYOUT

A-- James-- Alex-- Joanna--Brian

B-- Sarah

C-- Steve

D-- David-- Colin-- Peter

E-- Sally

I've been trying to use a pivot table but it won't allow to me output the data in this way (or perhaps I've missed something?).