Converting two columns table into one column table in Google Sheets

Question

In column A there is a list of tasks.

In column B each task has an associated group.

How to, using built-in formulas, generate sequence like in column D?

Here is a screenshot :

enter image description here

score 3 · Accepted Answer · answered Jul 05 '20 at 20:25

3

try:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(QUERY(IF(A2:B="",,A2:B&"♦"), 
 "select max(Col1) where Col1 is not null group by Col1 pivot Col2", 0)
 ,,999^99)),,999^99), "♦"))))

answered Jul 05 '20 at 20:25

player0

124,011
12
67
124

1

we gotta get you using FLATTEN()! :) – MattKing Jul 06 '20 at 14:32

score 1 · Answer 2 · answered Jul 06 '20 at 14:31

1

This should work as well as player0s. I keep trying to get him to use FLATTEN() :)

=QUERY(FLATTEN(TRANSPOSE(QUERY(A2:B,"select Max(A) group by A pivot B"))),"where Col1<>''")

answered Jul 06 '20 at 14:31

MattKing

7,373
8
13

Converting two columns table into one column table in Google Sheets

2 Answers2