CODEWITHSUNDEEP
arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query
Sławek Zawadzki
Sławek Zawadzki

Reputation: 37

Converting two columns table into one column table in Google Sheets

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

Upvotes: 2

Views: 189

Answers (2)

MattKing
MattKing

Reputation: 7783

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<>''")

Upvotes: 1

player0
player0

Reputation: 1

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), "♦"))))

0

Upvotes: 3

Related Questions