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

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

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

4. ## 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. ## 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. ## Re: Transpose duplicate customer email addresses from rows, to single row, multiple column

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

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

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