Transpose & Grouping

[Shelton_Thompson]

Hi Everyone, hope I can get help for this problem.

I have a workflow which looks for duplicates in records. I then use the grouping tool so I have single set's of duplicates to look at to ensure we do not mark the same record to both keep and deactivate. 

This results in this for example. As you can see there can be any number of matches from 1 to sometimes over 10.

Group	Key
6000000002	6500002038
6000000002	6500004673
6000000003	6500001977
6000000004	6500002028
6000000005	6500002034
6000000008	6500002031
6000000008	6500005622
6000000018	6000000701
6000000018	6000027351
6000000018	6500005486
6000000018	6500005715

In order to run the rest of my workflow to select a single survivor for each set of duplicates, I need to transpose the data to look like this where there is a single row for each group with the values filling each column. 

Group	Key 1	Key 2	Key 3	Key 4
6000000002	6500002038	6500004673
6000000003	6500001977
6000000004	6500002028
6000000005	6500002034
6000000008	6500002031	6500005622
6000000018	6000000701	6000027351	6500005486	6500005715

I have tried the cross tab function, but the only way I can think of to get that to work is to assign a ID # (which is essentially a count) to each match so it would look like this where the count restarts for each group but I am unsure what tool to use to do this.

Group	Key	Key ID
6000000002	6500002038	1
6000000002	6500004673	2
6000000003	6500001977	1
6000000004	6500002028	1
6000000005	6500002034	1
6000000008	6500002031	1
6000000008	6500005622	2
6000000018	6000000701	1
6000000018	6000027351	2
6000000018	6500005486	3
6000000018	6500005715	4

I also tried the transpose tool to no avail. Any Ideas?

[LordNeilLord · Accepted solution]

Hey @Shelton_Thompson

Multirow formula is the tool for this job!

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[NicoleJohnson]

You're almost there! Using the KeyID field, create another field that concatenates "Key " with your [KeyID] number for each record... and then use the Cross-Tab tool to get your data in the right layout. You will group by Group and then select your new field (the one that now reads "Key 1", "Key 2" etc.) as your header, and the Key field as your columns!

You could also use the Tile tool to get your KeyID numbers - always nice to see a new way to do it! Hope that helps :)

Cheers,

NJ

[Shelton_Thompson]

Thank you so much! Worked perfectly!