Summarizing Data from multiple sources down to a single row

[CNichols84]

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).  

Email	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: 

Email	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!

[braveraj]

There might be an easier solution but here's what I came up with.

[CNichols84]

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 :)

[braveraj]

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!

[braveraj · Accepted solution]

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.

[CNichols84]

Thank you very much @braveraj !