0

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!

4
  • Well, I used the view to add tables using the markup that is showing here. I have no idea why it is showing the markup instead of the tables. Please let me know how to fix this!
    – jguion
    Oct 7, 2021 at 18:16
  • The most efficient way to receive help from the volunteer contributors here will be if you share a link to a sample sheet with realistic sample data already entered, as well as the manually-entered results you want to see where you want to see them. Be sure to set the link permissions (when creating the link) to "Anyone with the link..." and "Editor," so that those here can access the sheet, test solutions, and leave any working solutions in the sheet for you.
    – Erik Tyler
    Oct 7, 2021 at 19:00
  • Please also indicate the exact single approach you would like to go with (e.g., comma-separated list, space-separated list, results in separate cells, etc.). To say, "I might want..." indicates that you expect the volunteers to provide several solutions for you, which will be a deterrent to many. If you think through which you want and specify it, your chances of inviting interaction will increase.
    – Erik Tyler
    Oct 8, 2021 at 13:14
  • Refer the answer by @player0 to Combine duplicate rows in column as comma separated values - Google Query on StackOverflow. Simply chnage the ranges in that answer from {A2:B, B2:B} to B2:C, C2:C and A2:B&",", B2:B to B2:C&",", C2:C.
    – Tedinoz
    Oct 8, 2021 at 14:50

1 Answer 1

0

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.