Help wuth transpose (perhaps ?)

I have a list of names in column A with a list of roles in column B. Several names are duplicated as they have more than one role. Is there a way of producing a report that would only list the name once and put all his roles in a horizontal position. Example attached. I don't mind if it requires a transpose, vlookup or formula. Very many thanks

Hi, and welcome to the forum.
Working with the layout in your example, copy the following into Cell H6, and then copy across how ever many different roles you have
and down how ever many (unique) performers there are. Your list of unique performers would be Col G (starting at G6). The formula currently
allows for up to 1000 performer names with their (single or multiple) roles in Cols A and B (starting at A6).
If you reposition the formula table, always place this formula in the top left data location and then copy across and down. Also, you will need to keep cols A and B sorted so that the records for one performer are
on adjacent rows (as now).

=IF(COUNTIF($A$6:$A$1005,$G6)>COLUMN(A$1)-1,INDEX($B$6:$B$1005,MATCH($G6,$A$6:$A$1005,0)+COLUMN(A$1)-1),"")

Good luck,

Hercules

That is absolutely brilliant and works a treat... BUT (isn't there always Using the example it appears that I have to physically enter the Performers Name into the G column. Is there any way at all that the formula could be adapted to look down column A and only put one occurance of the performer in column G. i.e It sees David three times in column A but only transfers the name once into column G. So grateful for your help so far.

Hi
You can do this using the Advanced Filter on the Data menu. Your multiple records (Col A) go in as the list range. You will want the unique list to start at a new location (G6), so click the radio button, tick "Unique Records only" and enter G6 in the "Copy to" Range.

Le Voila !

Hello,

I added the formula above (nice formula, btw ) to the uploaded file and added a helper and a formula to generate a unique list. I've seen other ways to do this in one step while still being a live cell, but this works better for me. Hope this helps.

Have a good day,

Guys these are amazing. Thank you both so very much indeed. Truly appreciate it.