Converting a 2D range into a 1D array with spaces in Google Sheets

user_19199332 0
Viewed 77

I would like to convert a range/array like this

| Fruit     | Strawberry |
| Fruit     | Blueberry  |
| Fruit     | Banana     |
| Vegetable | Lettuce    |
| Vegetable | Cucumber   |
| Vegetable | Carrot     |
| Vegetable | Celery     |
| Dairy     | Milk       |
| Dairy     | Butter     |
| Dairy     | Cheese     |

into a 1D array with spaces separating categories like this

| Fruit      |
| Banana     |
| Blueberry  |
| Strawberry |
|            |
| Vegetable  |
| Carrot     |
| Celery     |
| Cucumber   |
| Lettuce    |
|            |
| Dairy      |
| Butter     |
| Cheese     |
| Milk       |

in Google Sheets. I can easily achieve a 1D array without spaces with =UNIQUE(FLATTEN(A2:B11)) where A2:B11 is the original data range; however, I am not able to get the desired spaces separating categories.

Could anyone help?

Here's the example sheet: https://docs.google.com/spreadsheets/d/1ww1fkDRBUsVx-pLDJ4Qo9VXlzAZDEyYoANRdRK7Yum4/edit?usp=sharing

google-query-language flatten google-sheets filter arrays
4 Answers

try:

=INDEX(QUERY(FLATTEN(TRANSPOSE(QUERY({A2:B, ROW(A2:A)},
 "select max(Col2) group by Col3 pivot Col1"))), 
 "where Col1 is not null", ))

enter image description here

update:

=INDEX(SUBSTITUTE(QUERY(FLATTEN(IFERROR(SPLIT(FLATTEN(TRANSPOSE(
 QUERY({"♦×"&A2:A, B2:B, ROW(A2:A)},
 "select max(Col2) group by Col3 pivot Col1"))), "×"))), 
 "where Col1 is not null offset 2", ), "♦", ))

enter image description here

user_56326290

This is a draft to illustrate one possible approach:

=ArrayFormula(query(regexreplace(unique({"";flatten({A2:B999,if(A2:A999<>A3:A1000,row(A2:A999)&"","")})}),"\d*",""),"offset 1"))

enter image description here

Obviously needs generalising for sheets that don't have 1000 rows:

=ArrayFormula(query(regexreplace(unique({"";flatten({A2:B,if(A2:A<>{A3:A;""},row(A2:A)&"","")})}),"\d*",""),"offset 1"))
user_38949170

The other answers are great. This would also work

=ARRAYFORMULA(
  FLATTEN(
   QUERY(
    QUERY(
     {A2:A,B2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))},
     "select Col1, Max(Col2)
      where Col2 is not null
      group by Col1
      pivot Col3"),
    "offset 1",0)))

Its creating a pivot table using an iterative count of the categories, then a second QUERY to ditch those numbers, then flattens.

user_122540980

One more idea to add to the mix. There's a new tab on your sample sheet called MK.Idea with this formula in cell D2.

=ARRAYFORMULA(QUERY(FLATTEN(SPLIT(IF(A2:A11=A1:A10,," |"&A2:A11)&"|"&B2:B11,"|")),"where Col1<>'' offset 1"))
user_110751290
Related