Concatenate certain rows into a cell ignoring duplicates

Question

I have a google form and I would like to sort it's responses in a separate sheet on google sheets. The results of the form look sort of like this.

Id    Job
1     Shelving, Sorting
2     Sorting
1     Cleaning, Shelving
3     Customer Service
2     Shelving, Sorting

which I would like to format into

Id    Jobs
1     Cleaning, Shelving, Sorting
2     Shelving, Sorting
3     Customer Service

Is there a formula I can use to accomplish this, noting that it ignores duplicates and groups the different ids? Ordering of the jobs does not matter.

Answer 1

Working example here.

The code is like:

=unique(transpose(split(join(", ",filter(B1:B10,A1:A10=1)),", ")))

where

• filter(B1:B10,A1:A10=1) gives you all the B values where A = 1
• join(",", filter(...)) joins the list with the ", " separator (e.g. "apple, orange" and "kiwi" becomes "apple, orange, kiwi"
• split(join(...)) splits the list into an array (e.g. back to [apple, orange, kiwi]
• transpose(split(...)) converts the horizontal list to vertical list
• unique(transpose(...)) gives you the unique values (unique() only works with vertical list)

After this, you need to transpose then join the list

Note you must keep the separator consistent (e.g. always "," or ", ")