Find if a value exists in a Google sheet on a certain column, in all the rows above the current row based on 2 criterias

Ovi 0
Viewed 60

I have the following scenario:

  • columns from A-Z and 100 rows
  • in each row for the Z column I want to find if the value in A column from the current row exists in the rows above in A column
  • then if exists, I would like to find if the B column for the matching rows have the cell completed with a value
  • for all the rows that are matching I would to receive the matching rows in an array list, not as rows or at least to be able to put a value like "mathing"/"not matching"
  • this should be an array formula

I've tried something like this, only for the first criteria, but somehow it checks only the current row.

=ARRAYFORMULA( IF(ROW(Z2:Z)>2, IF(MATCH(A2:A,$A$2:A&ROW(A2:A)-1),"matching","not matching"),"not matching"))
  • I check to see if it's the first row (as it has headers), and if it's the first row, then surely it can't have any data matching above

It will be great to have it as a google sheet formula but if it's not possible it could also be a google app script

google-sheets-formula google-sheets google-apps-script
1 Answers

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  const dsr = 2;
  const vs = sh.getRange(dsr, 1, sh.getLastRow() - dsr + 1, sh.getLastColumn()).getDisplayValues();
  let o = [];
  vs.forEach((r, i) => {
    if (i > 0) {
      let as = vs.map(r => r[0]).slice(0, i);// suggested by DoubleUnary
      let bs = vs.map(r => r[1]).slice(0, i);//suggested by DoubleUnary
      let idx = as.indexOf(r[25]);
      if (~idx && bs[idx]) {
        o.push(['yes', dsr + i, dsr + idx, r[25], bs[idx]])
      } else {
        o.push(['no', dsr + i, ~idx ? as[idx] : '', r[25], ~idx ? bs[idx] : '']);
      }
    }
  });
  o.unshift(['Value', 'Test Row', 'Result Row', 'Z value', 'B value'])
  Logger.log(JSON.stringify(o));
  osh.getRange(1, 1, o.length, o[0].length).setValues(o);
}

My Data:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12 COL13 COL14 COL15 COL16 COL17 COL18 COL19 COL20 COL21 COL22 COL23 COL24 COL25 COL26
4 4 8 18 3 15 15 6 6 18 2 10 19 14 5 16 3 6 0 13 15 14 10 13 19 7
14 5 18 12 12 3 5 5 12 0 0 4 19 17 13 14 2 6 2 0 18 15 16 1 1 15
14 8 18 19 18 19 14 11 9 2 12 4 19 8 7 17 2 5 17 12 3 18 6 15 12 17
12 15 1 11 2 14 4 12 15 4 2 7 13 12 4 10 0 2 9 2 15 12 18 7 10 6
15 8 3 11 3 11 8 2 0 12 18 12 17 3 3 10 5 18 0 6 19 12 11 2 3 5
16 16 7 14 12 3 1 9 0 1 9 4 17 11 18 2 4 16 13 4 1 3 4 13 9 8
11 18 9 9 10 17 6 16 8 10 15 10 18 1 2 9 10 18 13 0 11 4 7 2 0 18
3 5 1 5 18 17 4 8 2 4 10 13 7 10 9 6 3 7 5 7 12 12 6 0 3 7
3 3 19 4 2 5 0 9 5 14 0 2 15 9 18 6 1 15 5 5 1 12 4 7 9 3
19 19 15 16 12 18 13 0 12 4 12 4 1 8 19 2 1 1 8 14 6 10 0 16 14 14
10 8 3 15 5 13 9 13 10 6 16 2 15 3 2 16 19 2 14 1 10 1 1 5 5 10
8 3 8 17 13 15 8 9 6 4 2 14 6 4 1 6 14 8 9 11 12 3 18 5 14
9 18 2 12 17 2 17 10 0 11 7 11 2 0 11 15 6 7 13 10 18 17 6 19 12 14
15 7 12 5 0 17 15 2 2 18 6 7 13 1 10 19 9 7 13 15 13 7 18 11 13 10
8 1 10 5 17 9 9 5 14 3 3 1 19 7 13 0 5 10 2 12 17 3 12 9 0 10
9 15 6 14 18 1 3 6 4 9 19 4 9 15 11 0 3 10 19 5 18 16 10 4 4 4
1 1 6 8 10 9 8 19 4 11 18 12 14 8 4 5 11 8 17 5 7 13 13 16 14 8
7 14 7 18 9 3 11 0 1 7 19 8 6 3 4 4 2 4 11 3 7 5 5 9 16 15
7 6 4 6 7 17 8 13 10 2 9 18 0 13 12 4 13 9 4 19 4 7 10 17 1 5
5 3 7 12 3 19 19 7 1 11 9 9 9 7 5 6 8 7 0 11 19 6 17 12 1 18

Results:

Value Test Row Result Row Z value B value
no 3 15
no 4 17
no 5 6
no 6 5
no 7 8
no 8 18
no 9 7
yes 10 9 3 5
yes 11 3 14 5
no 12 10
yes 13 3 14 5
yes 14 3 14 5
yes 15 12 10 8
yes 16 12 10 8
yes 17 2 4 4
no 18 8 8
yes 19 6 15 8
no 20 5
no 21 18
user30hu0
Related