# How to create multiple columns from a single one, combining rows with a common cell?

My data looks like this:

``````USERNAME  ENTRY_TYPE   ENTRY_CHOICE
user_a    ice_cream    lemon
user_b    ice_cream    strawberry
user_b    ice_cream    blueberry
user_c    ice_cream    chocolate
user_c    ice_cream    salt
user_c    ice_cream    powerpuff
``````

Given that there can be from one to three ice cream choices, how can I fit my dataset into one row per user?

``````USERNAME  OTHER_DATA  ICE_CREAM_CHOICE_1  ICE_CREAM_CHOICE_2  ICE_CREAM_CHOICE_3
user_a    [...]       lemon
user_b    [...]       strawberry          blueberry
user_c    [...]       chocolate           salt                powerpuff
``````
• Do you want a one-time conversion or is this about creating an extra ('live') view? The cuurent two answers are for the latter. May 6, 2021 at 10:11

In the image, I get the unique user names by using this formula in cell G2:

``````=UNIQUE(A2:A7)
``````

I use this formula in cell H2:

``````=TRANSPOSE(FILTER(\$C\$2:\$C\$7,\$A\$2:\$A\$7=\$G2))
``````

I then drag that formula down to the bottom of the list of usernames.

FILTER function is generally available to Microsoft 365 subscribers.

If you can not get this function, you may try the following.

For cell E2, use formula =INDEX(\$C:\$C,SMALL(IF(\$A\$2:\$A\$7=D2,ROW(\$2:\$7),4^8),ROW(A1)))&""