Joining and sorting strings: how to turn week menu into shopping list?

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?

• 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 Sep 20 '18 at 20:16
• @Datanovice I have updated my question with an example. I enter menu items into gsheets manually. – dzieciou Sep 20 '18 at 22:53
• 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 Sep 21 '18 at 8:08

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

``````=ARRAYFORMULA(TEXTJOIN(CHAR(10),TRUE,TRIM(SPLIT(JOIN(",",B2:B5,C2:C5,D2:D5,E2:E5,F2:F5),",",true,true))))
``````

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.

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 Sep 22 '18 at 10:14