Results 1 to 6 of 6

Thread: Help wuth transpose (perhaps ?)

  1. #1

    Help wuth transpose (perhaps ?)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    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 by Hercules1946; 2013-10-02 at 02:52 PM.

  3. #3
    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.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    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 !

  5. #5
    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,
    Attached Files Attached Files

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •