+ Reply to Thread
Results 1 to 8 of 8

Transpose duplicate customer email addresses from rows, to single row, multiple columns

  1. #1
    Registered User
    Join Date
    11-27-2009
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Transpose duplicate customer email addresses from rows, to single row, multiple columns

    Hello,

    I have an SAP file of customers and their email addresses. If there is more than one email address per customer then this will show as duplicate rows, with the same customer, and the different email addresses.

    I would like to have one row per customer and have the email addresses showing in separate columns, which can be up to 100 or more.

    How do I transpose each unique email address to a single row per customer, and remove the duplicate rows?

    eg: Data looks like

    Customer A customerA@Hotmail.com
    Customer B customerB@Hotmail.com
    Customer B customerB@Hotmail2.com
    Customer B customerC@Hotmail3.com
    Customer C customerC@Hotmail.com
    Customer C customerC@Hotmail2.com

    Result should be:
    Email 1 Email 2 Email 3
    Customer A customerA@Hotmail.com
    Customer B customerB@Hotmail.com customerB@Hotmail2.com customerC@Hotmail3.com
    Customer C customerC@Hotmail.com customerC@Hotmail2.com

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,808

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    I am unable to explain as it is not allowing me to post whole detail.

    Check the attached file
    Last edited by shukla.ankur281190; 08-17-2016 at 03:48 AM.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    A similar solution, also providing you with a unique list of customers.

    Unique customer list (array formula) in F2, copied down:
    =IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$7),0),0)),"")

    Email addresses, array formula, in G2, copied across and down:
    =IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$7=$F2,ROW($A$2:$A$7)),COLUMNS($A:A))),"")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Glenn



  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    I've just noticed that your profile states that you're using Excel 2003. If so, use this in F2
    =IF(ISERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$7),0),0))),"",INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$7),0),0)))

    and this in G2:
    =IF(ISERROR(INDEX($B:$B,SMALL(IF($A$2:$A$7=$F2,ROW($A$2:$A$7)),COLUMNS($A:A)))),"",INDEX($B:$B,SMALL(IF($A$2:$A$7=$F2,ROW($A$2:$A$7)),COLUMNS($A:A))))

    array entered as before.

  5. #5
    Registered User
    Join Date
    11-27-2009
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    Thanks, I struggled with this at first because I needed to put the unique customer list in another workbook, and must have been keying the formulae incorrect. Thanks for your support

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  7. #7
    Registered User
    Join Date
    11-27-2009
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    Hi Glenn, sorry that reply was for Shukla. I have also tested your formulae which also worked great, as I could keep it in the same worksheet. Thanks. I now only find that my excel is struggling with the calculation as my file is more than 4000 rows, and up to 100 email addresses, so 100 columns. Is there any solution to this? Also where can I read up on this formulae so I can understand the logic and write a formulae myself? I updated my profile as we now use office 2016.

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

    You could use a bit of VBA to provide the unique customer list - much quicker than any array formula. Interested?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose duplicate rows into columns
    By Ochenden in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-08-2016, 03:44 PM
  2. Replies: 0
    Last Post: 09-13-2015, 04:43 PM
  3. transpose duplicate rows to columns
    By slabbbe in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-17-2015, 12:21 PM
  4. Seperating single cell addresses into multiple columns
    By Tom_J_W in forum Excel General
    Replies: 7
    Last Post: 06-11-2013, 09:59 PM
  5. Replies: 5
    Last Post: 05-25-2013, 07:12 AM
  6. Replies: 1
    Last Post: 10-24-2012, 12:27 PM
  7. Replies: 2
    Last Post: 09-19-2012, 10:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1