1

I'm trying to mimic a pivot table behaviour where you would grab all distinct values from Col B and, by each of those unique Col B values, you associate all each respective values, that must also be distinct by each unique Col B.

Here's my data set:

A               B
y_13_other  4601032322-video
y_13_other  4601032322-video
o_06_other  4601032322-video1
o_06_other  4601032322-video
o_06_other  4601032322-video2

The final result should be:

 B                   A
4601032322-video   y_13_other
4601032322-video   o_06_other
4601032322-video1  o_06_other
4601032322-video2  o_06_other

I've tried the query with Group by, but it doesn't seem possible to aggregate by text.

=QUERY(A2:B5;"SELECT A,B group by B")

This would need an AGG, and it only seems to work with numbers.

Another option I thought was to pair a query with vlookup, but I haven't managed to, is there a way to do this, even without the query formula?

Note: I don't know how many rows A or B is going to have and don't know their future names, it would have to work dynamically.

Pivot Table example

Improve this question
1
  • you can use double query and agg with count then drop the coun column
    – user0
    Feb 15, 2019 at 14:05

1 Answer 1

Reset to default
0 

=QUERY(QUERY({A1:B}, 
 "select Col1, Col2, 
  count(Col2) 
  group by Col1, Col2", 0),
 "select Col2, Col1 
  where Col1 is not NULL 
  order by Col2", 0)

Improve this answer
1
  • That works perfectly! But is there any other way to do it? It feels like there must be a different way
    – John ED
    Feb 15, 2019 at 14:47

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.