Google-sheets – Joining and sorting strings: how to turn week menu into shopping list

google sheets

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.

example

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?

Best Answer

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.

https://docs.google.com/spreadsheets/d/1qONq_DpPdb7h8NYO2LYYlBc74oc_UMFM3Ob-TOZ9pNs/edit

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))))))
Related Topic