please help transposing data

[Jocelynupup]

Hi everyone,

I have this dataset. I am thinking about using cross tab to do it, but don't know how.

Data:

customer ID	customer name	product
1	A	food
1	A	pet
1	A	toy
1	A	bath
2	B	cloth
3	C	food
3	C	kitchen

I am trying to transpose to the following format:

result

customer ID	customer name	product No1	product No2	product No3	More?
1	A	food	pet	toy	Y
2	B	cloth			N
3	C	food	kitchen		N

Thank you for your help.

[CoG]

Just to check and make sure, do you only want a maximum of three Products in columns, or do you need/prefer all the columns to be Cross Tabbed? Also, if you want to truncate your results to three columns, how do you pick which product(s) you want to drop/treat as extra (with 'Y' in [More?] column)?

[Jocelynupup]

Good questions, I didn't think about this.

1. Yes, I only want maximum of 3 name of products. Otherwise, it's going to be endless.

2. If there are more three products, then we only randomly return the name of three products and mark it as "Y" in last column called [More?]. Otherwise, if there are less or equal than 3, it's going to be marked as "N".

[CoG · Accepted solution]

When many people say random, they don't actually mean random (I don't know which you are so I built 2 options for you) ;). Using one of @Qiu 's favorite tools... The Tile Tool!!!

[Qiu]

@CoG 
Thank you for noticing my favorite tool and hope you like it.😁

Just read your flow and it is smart one, specially the filter "[Tile_SequenceNum] < 5".
I was wondering where is the "5" coming from 😂

[Jocelynupup]

The workflow works even if the filter tool is taken away. 😁

[Jocelynupup]

The workflow works for my dataset! Thank you very much.