Help required on transposing data

Question

Hi, I have client which purchased multiple products. I would like to list the product IDs in row.

I have tried unique to remove duplicates, but those with different product ID is being removed as well. I tried transpose, but i could not achieve the output i needed.

I have attached a file. Could anyone please help?

Book 1.xlsx

Kenda · Accepted Answer

Hey @skychar

You were close, but it is actually the Cross Tab tool that will be your key here!

First, however, you need to create a new field that will be used as your headers. This can easily be done with the Multi-Row Formula tool. Simply group by Client ID and use this expression:

[Row-1:New Field]+1

Then, add your Cross Tab tool, grouping by Client ID, using New Field as your headers, and Product ID as the values for new columns. You can select anything for the method of aggregation, because we took care of this uniqueness when creating our new field.

Hope this helps!

fmvizcaino · Accepted Answer

Hi @skychar ,

Attached is a solution showing how to do it.

Take a look and let me know if you need further assistance.

Best,

Fernando Vizcaino