CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Get Job
Get Job

Reputation: 39

Adding a column by condition in Google Sheets

ARRAYFORMULA(LET(arr;A:A;string_arr;"A";main;FILTER(ROW(arr);REGEXMATCH(arr;" – "));range;{string_arr&main&":"&string_arr&{CHOOSEROWS(main;SEQUENCE(COUNTA(main)-1;1;2));COUNTA(arr)+1}-1};BYROW(range;LAMBDA(x;TRANSPOSE(INDIRECT(x))))))

Currently, there is a formula inside the file that does:

  1. output all cells from column "A" with a long dash (-) to column "now"
  2. Output all cells from column "A" after the long dash into the row next to it, BEFORE the next cell with the long dash

How do change the formula so that in addition to the current formula, all cells that begin with "BASKETBALL." and refer to the match next to it are output from column "A" to the "league" column?

I manually made a sample next to it in the file. https://docs.google.com/spreadsheets/d/1bMcBPiWpreLRGwfwZJOW8eAWgS10DTc-lY3Y5YSqyH8/edit#gid=745011392

Upvotes: 0

Views: 57

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30240

You may try:

=let(Σ;scan(;A3:A;lambda(a;c;a+regexmatch(c;" – ")));
     Δ;scan(;A3:A;lambda(a;c;if(left(c;10)="BASKETBALL";c;a)));
       map(unique(filter(Σ;Σ<>0));lambda(z;{+filter(Δ;Σ=z)\torow(filter(A3:A;Σ=z);1)})))

enter image description here

Upvotes: 1

Related Questions