How to tranpose 2 column excel spreadsheet by column A group

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?).

Sorry to bump this, just wondering if anyone had any ideas?

It seems quite straight forward so I'd be surprised if there was no way of doing this. I just can't seem to figure out.

Any tips would be greatly appreciated.