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

Question

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

because it might help with a solution, is there a reason for that desired layout? — MattKing, May 25 '22 at 20:00
Your post shows the food items from Col B in alphabetical order, but the categories from Col A in the order in which they appear in the original data set. Is this significant? Or can both the categories and items be alphabetized, even if that means that the order of categories changes from the original order? — Erik Tyler, May 26 '22 at 03:25
@ErikTyler Thanks for pointing this out. Yes, both the categories and the items should be alphabetized (and I should have written my example to reflect this). — Noah Hester, May 27 '22 at 18:37
@MattKing The desired layout is a preferable format for a small budgeting sheet! — Noah Hester, May 27 '22 at 18:40

player0 · Accepted Answer · 2022-05-26T09:43:59.967

3

try:

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

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

edited May 26 '22 at 09:43

answered May 25 '22 at 21:13

player0

124,011
12
67
124

1

Pivot is a neat way to do it, wish I had thought of that. Just need to add spaces between groups to get what OP has asked for I think? – Tom Sharpe May 26 '22 at 06:52
@TomSharpe yes, you are right. updated... – player0 May 26 '22 at 09:46
1

@player0 Thanks so much for this clever and neat solution! – Noah Hester May 27 '22 at 18:46
@player0 you might like mine too :) – MattKing May 28 '22 at 15:31
@MattKing nice one in some scenarios, tho I broke it a bit :P https://i.stack.imgur.com/kLSvk.png – player0 May 28 '22 at 19:12
1

@player0 fixed by adding ,0,0) to the SPLIT() – MattKing May 29 '22 at 11:56

Tom Sharpe · Answer 2 · 2022-05-26T06:50:04.193

2

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

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

edited May 26 '22 at 06:50

answered May 25 '22 at 20:56

Tom Sharpe

30,727
5
24
37

score 2 · Answer 3 · answered May 27 '22 at 00:24

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.

score 1 · Answer 4 · answered May 28 '22 at 15:30

1

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

answered May 28 '22 at 15:30

MattKing

7,373
8
13

UPDATED to add ,0,0) to the SPLIT(). – MattKing May 29 '22 at 11:55

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

4 Answers4

update: