2

Here's the pivot table I have in excel: I have a list of website with their emails address. Sometime you have one email per website, sometime you have 3 emails per website. I want to transpose the multiple emails I have for one website that are in column Email 1 into multiple field such as Email 1, Email 2, Email 3 for EACH corresponding websites.

Here's an example of my pivot table:

**URL | Email **

site.com | Email 1

       |Email 2

site2.com | Email 1

site3.com | Email 1

       |Email 2

       |Email 3

site4.com | Email 1

I want to turn it into:

URL | Email | Email 2| Email 3

site.com | Email 1| Email 2

site2.com | Email 1

site3.com | Email 1| Email 2| Email 3

site4.com | Email 1

Sorry about the format. So the idea is simple, I just need to transpose the multiple emails I have for a website into columns. but when I put the email field into Column Label, it takes every emails and instead of 3 emails columns labels (since I have no more than 3 emails per website) I have 7 column labels.

Any idea of who to achieve that?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3353255
  • 35
  • 2
  • 4

1 Answers1

3

I wish Excel's pivot tables had better functionality for this, but I'm not aware of any way to get it directly.

That said, you can achieve this with some formulas:

1) Add a COUNT and LOOKUP column to your data

SCreenshot of additional formulas

2) Make a pivot table to get a unique list of URLS

3) Use IFERROR combined with VLOOKUP to fill in your transposed table:

Screenshot of lookups

Here is a sample workbook showing how it all comes together.

Tim Sands
  • 948
  • 4
  • 8
  • Thanks so much Tim, that does it for me!!This is saving me a huge ammount of time...kudo to you!!That's gonna be much easier to import everything into a dB. – user3353255 Feb 26 '14 at 14:15
  • Glad it helped. If you can mark the answer as accepted it will help other people who might come across the same question. – Tim Sands Feb 26 '14 at 18:49