Log In
Register
Home
New Member
August 21, 2020
Offline
Hi,
Hoping to learn the best way to transform a typical vertical list into a horizontal one that is de-duped by one criteria, with the related data from the 2nd column transposed to many columns based on how many instances occur. The purpose is for cleaning up an email list prior to importing in an ESP, and using the relational data to populate a dynamic section of html to each single contact.
Column A "Advisor Email" will have duplicates
Column B "Account-Guest Name" will all be unique
I need to 1. De-dupe by the Emails and 2. transpose the Account- Guest names to Columns beside their related advisor email. Need to quickly de-dupe and reformat for 1:1 and 1:Many relationships.
A very small sample file is attached to demonstrate. In real life, this list could be from 20K-100K rows with up to 50 matches for one advisor. Have Power Query but am very beginner and still learning, and always teaching myself new Excel functions. But have not figured out the easiest way to do this task.
Help! thanks!
Amy
VIP
Trusted Members
December 7, 2016
Offline
Hello,
No need for Power Query for this. See attached file for one example on how this can be achieved.
Just add a new column to your table where you count how many times each Advisor Email occurs, then create a Pivot Table, be sure you load the data to the Data Model as the data you want to see as value is text, it is needed to create a Measure and for that it needs to be in the Data Model.
Hope this turns out useful for you.
Br,
Anders
New Member
August 21, 2020
Offline
Hi Anders,
thank you! I was able to back track along and recreate what you have shown within the real file. It will work! And I see where I can spend some extra time learning Measures. Very helpful.
Thanks again!
Amy
1 Guest(s)
