CODEWITHSUNDEEP
excelrowpivot-table
user3353255
user3353255

Reputation: 35

Excel pivot table: How to transpose multiple value in column to a column labels

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?

Upvotes: 2

Views: 6987

Answers (1)

Tim Sands
Tim Sands

Reputation: 1068

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.

Upvotes: 3

Related Questions