Crosstab

[aparna0208]

Hi,

Looking for some help!

I have an input excel file with contact information. A contact appears many times and can have multiple roles. I need to make the list of reps distinct and then need to flatten the multiple roles into single row per contact. Need to have all the roles separated by a comma in one row per contact. I tried this logic using group by and cross tab tool but not sure about the column headers and values for new columns field in crosstab tool. Any help on this would be great. Thank you in advance!

Input

Name                           Phone                                        Email                                  Role

Bob Smith                     1234                                       bob.smith@abc.com             Manager

Bob Smith                     1234                                       bob.smith@abc.com             Branch Head

Bob Smith                     1234                                       bob.smith@abc.com             Market Head

David Williams              5678                                       david.williams@xyz.com       Assistant

David Williams              5678                                       david.williams@xyz.com       Market Head

Desired Output

Name                           Phone                           Email                                       Role(New Column)

Bob Smith                    1234                            bob.smith@abc.com                 Manager,Branch Head,Market Head

David Williams              5678                           david.williams@xyz.com           Assistant,Market Head

[JosephSerpis]

Hi @aparna0208 you can do this with a summarise tool. I mocked up a workflow let me know what you think?

[jacob_kahn]

Hey @aparna0208  

I think this is pretty simple - all you need is one tool.

Please like and accept if this helps!

[RobertOdera]

Hi, @aparna0208 

Try this.

The workflow is attached.

Cheers!

[aparna0208]

Thanks for the quick response @JosephSerpis @jacob_kahn 

But when I tried @JosephSerpis solution it doesn't seem to work and I still see multiple rows having multiple roles

[jacob_kahn]

A pleasure to work with you!

[JosephSerpis]

Hi @aparna0208 can you share an example file so we can provide a solution.

[aparna0208]

Thanks for the inputs! @RobertOdera This workflow is what I am looking for but for some reason I'm not getting this output when I try it with my data.

I am attaching the excel file with the data I'm working on @JosephSerpis 

[JosephSerpis]

Hi @aparna0208 to get your data into 4 rows e.g. one row per Individual name. I had to concatenate Branch Name, Department Type and Individual Role. 

[aparna0208]

@JosephSerpis I am not able to open the attachment. Looks like the extension is .yxzp and I believe it's a workflow package