Creating new columns based on repeat information by Unique Identified Number

[aehrenwo]

I have a process I am currenty using an Excel VBA macro to handle an I am hoping there is a way to do this programtically in Alteryx. 

The data has unique User ID records numbers. There is then a column for the "Business Rule" they are in. In most cases each user ID is in 1 or 2 of these and would be represente in the data by multiple rows.

Original data

User ID	BusinessRule
123	100
123	101
234	200
234	201
234	202
567	300

I created a script that places each addtional role in a new tab with columns BusinessRule1, BusinessRule2, etc. 

The most # of rules that someone is in is 4. 

Result required

User ID	BusinessRule1	BusinessRule2	BusinessRule3
123	100	101
234	200	201	202
567	300

Is there a way to do this? Can it be set up to automatically add addtional columns if there are 5 unique rules a user is in (or does that have to be hard coded? 

Appreciate the input. 

Regards,

Adam

[JohnJPS · Accepted solution]

Hi @aehrenwo

One solution is to Transpose, then use  MultiRow formula to help determine the new column names; then CrossTab; (see attached).

[michael_treadwell]

The Crosstab tool will do what you need if you create a header column using the Multi-Row Formula tool

Edit: Or what @JohnJPS said

[slarosa_az]

Thanks for this. Was spending hours trying to do this in an iterative macro and this made it so easy!