Let's go with my new headache to convert recipes data to a more friendly format (I edited my original question thanks to adminstrators comments):

Example:

Let's go with my new headache to convert recipes data to a more friendly format (I edited my original question thanks to adminstrators comments):

Example:

The expected output:

Overall, the raw format is like this:

Number of recipes is variable, one title per recipe (maybe I can add a manual tag to identify it ?). Number of ingredients is variable and just below the same number of weights. At the end one cell contains the process to realize the recipe.

The wished transformed data:

Any idea how to achieve this?

• google-sheets

• "what I can do is to put a tag to identify titles": Create a new column with the function left(A1,5)="Title" (Provided column A is the column holding your data) – Luuk Commented Mar 30 at 8:24
• @jonrsharpe: Thanks for the re-edit, which i tried already (stackoverflow/revisions/79544321/2) , but it got reverted withing 10 minutes.... – Luuk Commented Mar 30 at 9:13
• Please edit the question and show realistic-looking sample data instead of mickey mouse data like Title 1, AAA, 111. It is currently unclear whether titles can be pattern matched, or if they need to be identified by the fact that they always immediately follow a number. – doubleunary Commented Mar 30 at 9:26
• When having a table on stackoverflow, please do not pre-pend all lines with a couple of spaces.... – Luuk Commented Mar 30 at 12:36
• 1 @Fabrice: do some research, and find: meta.stackoverflow/a/334688/724039 – Luuk Commented Mar 30 at 16:30

3 Answers 3

Here's one approach you could test & improvise on. goes along with your idea to manually mark the title row.

=reduce(tocol(,1),A:A,lambda(a,c,vstack(a,if(offset(c,,1)="T",let(Λ,xmatch("T",offset(c,1,1,rows(A:A),)), Σ,tocol(offset(c,1,,ifna(Λ-2,rows(A:A))),1),
 vstack(c,,"Ingredients",let(x,wrapcols(Σ,rows(Σ)/2),index(choosecols(x,2)&" "&choosecols(x,1))),,"Process",ifna(offset(c,Λ-1,),index(A:A,match(,0/(A:A<>"")))),,,)),tocol(,1)))))

You can try this approach using Google Apps Script

You can also achieve your desired output with the help of Google Apps script as it gives you more flexibility and functionality in terms of data structure.

Script Used

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange("A1:A36").getValues().flat(); 

  const copyR = [
    [0, "D1", true],  
    [17, "D11", false], 
    [18, "D13", true],  
    [35, "D23", false]  
  ];

  copyR.forEach(([src, target, bold]) => {
    const cell = sheet.getRange(target);
    cell.setValue(data[src]);
    if (bold) cell.setFontWeight("bold");
  });

  sheet.getRange("D2").setValue("Ingredients").setFontWeight("bold");
  sheet.getRange("D14").setValue("Ingredients").setFontWeight("bold");

  const copyB = [
    [1, 9, "E3:E10"],   
    [9, 17, "D3:D10"],  
    [19, 27, "E15:E22"], 
    [27, 35, "D15:D22"]  
  ];

  copyB.forEach(([start, end, target]) => {
    sheet.getRange(target).setValues(data.slice(start, end).map(v => [v]));
  });

  sheet.getRange("D10").setValue("Process").setFontWeight("bold");
  sheet.getRange("D22").setValue("Process").setFontWeight("bold");

  const combineRanges = [
    [3, 10], 
    [15, 22] 
  ];

  combineRanges.forEach(([start, end]) => {
    for (let i = start; i <= end; i++) {
      let columnD = sheet.getRange(`D${i}`);
      let columnE = sheet.getRange(`E${i}`);
      let dValue = columnD.getValue().toString().trim();
      let eValue = columnE.getValue().toString().trim();
      if (dValue && eValue) columnD.setValue(`${dValue} - ${eValue}`);
    }
  });

  sheet.getRange("E3:E10").clearContent();
  sheet.getRange("E15:E22").clearContent();
}

Sample Output

You have lists of recipes (dishes), each with its own ingredients, ingredient quantities and preparation process. You want to format the ingredients/process by Recipe.

This answer takes a slightly/very different approach.

• It enables the user to select a recipe from a dropdown list,
• Ingredients and process will be automatically populated
• Format of the "Process" will be set to wrap.

The answer consists of a dropdown, one formula and an onEdit script (to set the "Wrap" strategy for the process).

1. Define the Recipes, Ingredients and Quantities

• Create a sheet "Recipes" and populate with dish and process information (as shown).
• Create a sheet "Ingredients" and populate with unique ingredient names (as shown).
• Create a sheet "Quantities" and populate with dish name, ingredient name and ingredient quantity (as shown).
  • Dish names are selected by dropdown (Data validation linked to "Recipes")
  • Ingredient names are selected by dropdown (Data validation linked "Ingredients")
• Create an output sheet "Output"
  • Create a dropdown (Data validation linked to "Recipes") in cell A1 to select a recipe/dish
  • Insert this formula in cell A3:
    =arrayformula({query({Quantities!A1:A,Quantities!C1:C&" "&Quantities!B1:B},"select Col2 where Col1='"&A1&"' label Col2 'Ingredient'",1);{" "};query({Recipes!A1:B},"Select Col2 where Col1 is not null and Col1 = '"&A1&"'",1)})
  • Insert this script in the script editor:

function onEdit(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG
    
  var col2Watch= 1
  var row2Watch = 1
  var sheet2Watch = "Output"
  
  // test for change of recipe in Cell A1 on outputSheet
  if (e.range.getSheet().getName() == sheet2Watch &&  e.range.rowStart == row2Watch && e.range.columnStart == col2Watch){
    // matched the right sheet and cell
    Logger.log("matched the right sheet and cell")

    // get last row in column A
    var sheet =  e.source.getSheetByName(sheet2Watch)
    var lrow = sheet.getLastRow()
    var aVals = sheet.getRange("A1:A"+lrow).getValues()
    var aLast  = lrow - aVals.reverse().findIndex(c=>c[0]!='')

    // format the wrap of the "process"
    sheet.getRange(aLast,1).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
  }

}

SAMPLE OUTPUT-Tiramisu

SAMPLE OUTPUT-Toast

Recipes sheet

Ingredients sheet

Quantities sheet