Multiple rows but only one Unique ID - need to add to another column and remove the row

[DBParker]

I am new to alteryx and have an issue where my data has unique ID's but one of the columns can have multiple values, leading to a duplication of the unique ID in a table.  An example is below:

ID	Rep
1	John
1	Dave
2	Rob
3	Scott

I need to modify the output so that it comes out like below instead:

ID	Rep1	Rep2
1	John	Dave
2	Rob
3	Scott

Essentially, if it finds that the data has multiple Rep's for any of the unique ID's listed it would add a new column for each rep.  If there are 3 different Reps for any one ID then it would add two additional columns instead of just one and so on.

I have been unable to find a way to do this so any help would be appreciated!

[Bob_Blackey · Accepted solution]

Hi,

Here's one way to do it:

MULTI-ROW FORMULA: Add a rep number for each rep within an ID  (note the Group By check box)

FORMULA: Build the Rep1 , Rep2 text

CROSSTAB: Pivot the data

Cheers,

Bob

[DBParker]

This adds the additional column for ID 1, however, it continues to add columns for each ID beyond that as well.  See below:

ID	Rep1	Rep2	Rep3	Rep4
1	John	Dave
2			Rob
3				Scott

I need for Rob & Scott to be in the Rep 1 column since they are the only reps for that ID.  Plus in a larger data set this would have the potential of adding a very large number of columns that are not needed.

[Bob_Blackey]

Hi,
When I run it the results are in two columns as desired.
In the multi row formula tool I would check that you clicked ID in the "group by" section. That will reset the counter on every change in ID.

Cheers,
Bob

[DBParker]

That was the key.  Thanks much!