2

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?

  • 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 – Datanovice 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
  • 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)))) – Datanovice Sep 21 '18 at 8:08
1

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))))))
  • 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
  • Sort by what alphabetical order? – Datanovice Sep 22 '18 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 Sep 22 '18 at 10:57
  • 1
    I have no idea, especially the case insesntivie bit, will have to play around! – Datanovice Sep 22 '18 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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