Help wuth transpose (perhaps ?)

teammpc

New member
Joined
Oct 2, 2013
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Book1.xlsx
    9.1 KB · Views: 58

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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

 
Last edited:

teammpc

New member
Joined
Oct 2, 2013
Messages
3
Reaction score
0
Points
0
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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 !
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

Attachments

  • Role_sample.xlsx
    9.9 KB · Views: 57

teammpc

New member
Joined
Oct 2, 2013
Messages
3
Reaction score
0
Points
0
Guys these are amazing. Thank you both so very much indeed. Truly appreciate it.
 
Top