Every Sunday I define with my girlfriend a menu for the coming week. We use Google Sheets for that: each cell is a comma-separated list of products to prepare a meal. We enter those data manually. Then I manually create a shopping list of products to buy, going through the menu. How can I automate this process?

I was thinking about the following algorithm:

  1. Concate products from selected cells into an array.
  2. Split each element of the array by comma, so that the result is an array of arrays of single products.
  3. Flattenize the output of the previous step, i.e., turn the array of arrays into 1-dimensional array of products.
  4. Remove duplicate products or at least sort them lexically.
  5. Join all products into a single string with comma-separated elements, so that the final output can be displayed in a single cell.

Here's an example of how it works with input (selected menu) and output (shopping list). Click the screenshot to go to the original document.


I have programming background (Java), but I am not familiar with Google Sheets abstractions, e.g., whether there's a concept of an array.

Is my algorithm implementable in Google Sheets? What functions should I use?

  • 1
    How do you get the menu items into gsheets, do you import the html, scrape the data or manually type it in?do you mind creating an example sheet and a desired output tab
    – Umar.H
    Commented Sep 20, 2018 at 20:16
  • @Datanovice I have updated my question with an example. I enter menu items into gsheets manually.
    – dzieciou
    Commented Sep 20, 2018 at 22:53
  • 1
    I didn't remove the duplicates but you can use =UNIQUE() to do that, try : =ARRAYFORMULA(TEXTJOIN(CHAR(10),TRUE,trim(split(JOIN(",",B2:B5,C2:C5,D2:D5,E2:E5,F2:F5),",",true,true))))
    – Umar.H
    Commented Sep 21, 2018 at 8:08

1 Answer 1


Adding this as an answer, got your desired result (bar the commas), finally got around to testing it.


Working backwards,

  1. first we join our cells using the JOIN command, essentially creating an array.
  2. we then pass the SPLIT function to split this array.
  3. we apply TRIM to remove and leading and trailing white space.
  4. We use the TEXTJOIN command passing CHAR(10) (ASCII code for end of line) as our argument.
  5. we finish using an ARRAYFORMULA which actually is necessary for the TRIM function. I'm not 100% sure why, but my guess would be the trim needs to be inside an array to function across a wide-range of cells.

and there you have your groceries.

Sheet as an example.


EDIT : Updated Criterion:

the best I could do is pass a sort and a transpose which sorts the cell but the case-insensitive bit is beyond me, maybe someone else can help?

=ARRAYFORMULA(TEXTJOIN("," & CHAR(10),TRUE,sort(TRANSPOSE(trim(split(JOIN(",",B2:B5,C2:C5,D2:D5,E2:E5,F2:F5),",",true,true))))))
  • Thanks. What would be the way to sort the groceries list? I've tried to enclose TRIM function with SORT or SORTN functions and it didn't work.
    – dzieciou
    Commented Sep 22, 2018 at 10:14
  • Sort by what alphabetical order?
    – Umar.H
    Commented Sep 22, 2018 at 10:37
  • Yes, case-insensitive alphabetical order. E.g. "Eggs, Beef, Chicken, Rice, food (15), rice" should become "Beef, Chicken, Eggs, Rice, rice, food (15)".
    – dzieciou
    Commented Sep 22, 2018 at 10:57
  • 1
    I have no idea, especially the case insesntivie bit, will have to play around!
    – Umar.H
    Commented Sep 22, 2018 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.