I have an

The extract would give in the first cell of the "Extract" sheet : (149,163)-G:AD

Would someone be able to help me with that please ? If at all possible.

CodePudding user response：

Find non ok's

function wierdData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName('Sheet1');
  const r1 = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues().flat();
  const c1 = sh.getRange(1, 1, sh.getLastRow(), 1).getDisplayValues().flat();
  const ro = r1.findIndex(e => e != '')   1;
  const co = c1.findIndex(e => e != '')   1;
  const vs = sh.getRange(co, ro, sh.getLastRow() - co   1, sh.getLastColumn() - ro   1).getValues();
  let o = [];
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      if(isNaN(c) && c.toLowerCase() != "ok") {
        o.push(`(${r1[ro   j - 1]},${c1[co   i - 1]})-${c}`);
      }
    })
  })
  //Logger.log(JSON.stringify(vs))
  Logger.log(JSON.stringify(o));
  let oA = o.map(e => [e]);
  osh.clearContents();
  osh.getRange(1,1,oA.length,1).setValues(oA)
}
Execution log
11:33:56 AM Notice  Execution started
11:33:56 AM Info    ["(153,168)-S:AB","(149,163)-G:AD","(150,157)-G:CB"]
11:33:57 AM Notice  Execution completed

Sheet0:

Sheet1:

CodePudding user response：

I have added a new sheet ("Erik Help") with the following formula in A1:

=ArrayFormula({"NOT OK";QUERY(FLATTEN(IF((Survey!A:A="") (Survey!B:M="") (Survey!B:M="ok") (TRIM(Survey!B:M)="");;"("&Survey!B1:M1&","&Survey!A:A&")-"&Survey!B:M));"WHERE Col1 Is Not Null ORDER BY Col1")})

This formula will produce the header "NOT OK" (which you can change from within the formula as you like) and all results that are not listed as "OK" (any capitalization) in the Survey sheet. Results will be listed in the requested format.

Essentially anything where Col A in the Survey sheet is blank OR where Row 1 in the Survey sheet is blank OR where the rest is not some form of "OK" OR where any cell in the original grid is blank will be replaced with null. The rest will be concatenated with the Col-A and Row-1 data as requested. FLATTEN will form one column from that grid of results. And QUERY will remove null entries and place the final results in ascending order. If you'd prefer the results to run in descending order, just add DESC after Col1 at the end of the QUERY clause.