1

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
1
  • Do you want a one-time conversion or is this about creating an extra ('live') view? The cuurent two answers are for the latter.
    – Jan Doggen
    May 6, 2021 at 10:11

2 Answers 2

2

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.

enter image description here

0

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)))&"" enter image description here enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .