I'm creating a reference contact list. My goal is to get each unique Email down to a single row.
My original plan was to GroupBy 'Email' and then Concatenate 'SourceFile' and this is where I have started running into problems - I was also doing GroupBy 'Campaign' and 'Phone', originally expecting those Groups to end up with only one row since there is only one data point for each of those, but in my inexperience, it was also keeping a blank row for each of those (thus preventing me to get down to a single row by email). Regarding 'Title', I think it would be best to concatenate them, and then also break each individual Title into a new column (so they can be filtered appropriately).
SourceFile | Title | Campaign | Phone | |
abc@gmail.com | Mailing List | Director | ||
abc@gmail.com | SFDC | Manager | FY23 | |
abc@gmail.com | Web | Director | 555-555-5555 |
The end goal I am imagining is:
SourceFile (concat) | Title (concat) | Title1 | Title2 | Campaign | Phone | |
abc@gmail.com | <Mailing List> <SFDC> <Web> | <Director><Manager> | Director | Manager | FY23 | 555-555-5555 |
I am hoping someone with more experience can help this newbie out!
Thank you very much in advance!
Solved! Go to Solution.
Thank you very much for this @braveraj !
It works in the limited example I gave - that is really my fault for not giving the full context - but I'll have 10k+ rows, so as you can imagine once you crosstab the RecordID it goes wild with so many records. Right now i'm using what you proposed as a foundation and trying to replace RecordID with Tile, and seeing if I can get there, I'll update if I can figure it out.
Thanks for the help and I'm open to any more suggestions with this added wrinkle :)
you might be able to do a record ID thing but with a Row Rank formula tool but group on your primary key like email. Let me know what works!
Hi @CNichols84
Here's an alternative approach. If the only field you need broken into separate columns is title then you could concatenate and separate title in an offshoot. Meanwhile concatenate sourcefile, campaign, phonenumber, etc. separately. Instead of RecordID do the multi row formula tool and make sure to group by your primary key whatever that may be.
Thank you very much @braveraj !