Google-sheets – Matrix multiplication-esque concatenation

google sheets

I'm making a simple recipe maker in google docs, using row vectors and sumproduct I can get it to calculate the protein and calories for a given recipe. The way it's set up it can be hard to read the recipe so I would like to implement something that can add the strings of the ingredients and amount of it then add all those to create a recipe string.

As you can see I need it to concatenate the number in a given row by the ingredient and then concatenate all of them. For example "Recipe in this row" should say "1 dl milk, 1 dl proteinpowder, 1 egg". I get how to do this for a fixed number of ingredients but I want to be able to add ingredients and would need to have some type of running index. I'm new to spreadsheets and have no idea how to do it.

Best Answer

My initial idea is to do something like this: =IF(B2>0, CONCAT(B2,B1)&", ", "")&IF(C2>0, CONCAT(C2,C1), ""). This example only does 2 columns. Add more if statements for more columns. There is probably a better way to do this, but hopefully this gives you an idea of how you could solve this.

This puts the full recipe in one cell.

Related Solutions

Google Sheets – How to Split Multiple Rows or Insert Below Existing Rows

There is no default way to do it through UI of Google Spreadsheets. However, I have written a small script that you can use on your Google Spreadsheet to add rows.

function addRows(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();

  for (var i = 1; i <= numRows*2 - 1; i+=2) {
   sheet.insertRowAfter(i);
  }
}

To use this code, follow these steps.

In Google Spreadsheets, navigate to Tools > Script Manager.

screen shot from Google Spreadsheets showing menu option

Click New in the next window, a blank code.gs file will be opened. Copy and Paste the above code in Code.gs and save the project.

Come back to the tab that contains your original document. Open script manager again if it is not already open. Click Refresh at top right. Now you will see the function "addRows". In the screenshot you will see other function that I have created earlier, but you will only see one unless you already created some other function. Select it and click Run. Now rows will be inserted in your spreadsheet at once.

list of functions in script manager

Google Sheets – A Function Opposed to SUMIF

Have you tried <>?

=SUMIF (A1:A38, "<>Liverpool", B1:B38) + SUMIF(D1:D38, "<>Liverpool", C1:C38)

Not that the <>-symbols should be inside the quotation marks.

Best Answer

Related Solutions

Google Sheets – How to Split Multiple Rows or Insert Below Existing Rows

Google Sheets – A Function Opposed to SUMIF

Related Topic