Crosstab

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

Transformation

Accepted answers

RobertOdera

Hi, @aparna0208

No worries.

Looks like your actual case is a little more complex than the sample.

Assuming that you still only want Name, Phone, Email, and Role in the output then try the below (gave you two options).

If you need all the other fields to come into play then we might need to insert Regex at the end to remove duplicate roles. So let us know if we need to go there!

The workflow is attached.

Cheers!

For_aparna0208_2.yxmd

JosephSerpis

Hi @aparna0208 amended workflow let me know what you think?

Concante_18062020.yxmd

All comments

JosephSerpis

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

Summarise_18062020.yxmd

jacob_kahn

Hey @aparna0208

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

Please like and accept if this helps!

Summarize Tool.yxmd

RobertOdera

Hi, @aparna0208

Try this.

The workflow is attached.

Cheers!

For_aparna0208.yxmd

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!

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

Contact_Info.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703