How to get list of values in one column for distinct values in another column

Question

I need to do some analyses on a set of data for work and cannot figure out how to do this. I am familiar with Google Sheets QUERY, but could not do what I want and could not find anything like it using searches in Google.

Here is a very simple data set:

Name	Category	Value
Test1	A	1
Test2	B	2
Test2	C	3
Test2	A	4
Test2	B	5
Test2	C	6

Here are the desired results:

Category	Value
A	1,4
B	2,5
C	3,6

Or the list of values could be space separated. Or the list of values could have each value in a separate cell in the row. I might want to sort the values, descending numerically.

A simple query to get unique Category values:

=QUERY(UNIQUE($B$2:$B:$7), "SELECT Col1",1)

This gives me:

A
B
C

Any ideas or pointers would be appreciated!

Answer 1

This is answered by @player0 in Combine duplicate rows in column as comma separated values - Google Query on StackOverflow. Since it requires some adaptation to the OP's scenario and I dislike answers included in comments, the following is provided.

Use this formula

=ARRAYFORMULA({QUERY(QUERY({B2:C, C2:C}, "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3"), "select Col1 offset 1", 0), REGEXREPLACE(TRIM( TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({B2:C&",", C2:C}, "select max(Col2) where Col1 is not null and Col2 <> ',' group by Col1 pivot Col3"), "offset 1", 0)),,999^9))), ",$", )})

---

Sample