CODEWITHSUNDEEP
google-sheetsarray-formulasserial-number
Aashit Garodia
Aashit Garodia

Reputation: 410

Arrayformula to get a specific serial numbering

Google Sheet Link

enter image description here

NAME QUALITY CATEGORY SR.NO.
Monu gffg B 1
David rtet G 1
John dsadas Q 1
John iop Q 1
John bvn A 2
John Bp
Monu hhsd Y 2
Monu dfghfh R 3
Monu hgfh
David ljk F 2
David rty G 1
David wsddBPmer V
Monu jhgn Y 2
David ghf A 3
John dfghfh Q 1
John fgdfg C 3
John fdbv
John gdf A 2
Kelly dgf X 1

I want an ArrayFormula at D1 which exactly gives the same result as shown.

Criterias:

  1. Each unique name has a different serial number.
  2. Each unique name with same category has same serial number, with different category would have different serial number.
  3. Name with CATEGORY blank would not have serial number.
  4. Name with QUALITY containing "bp" (anycase) would not have serial number.

Upvotes: -1

Views: 96

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

OP asked me to put forward an answer based on a previous question. Here is my suggestion, but it probably won't address the issues referred to in @Aashit's comment to @rockinfreakshow which I have only just read.

=ArrayFormula(let(bp,regexmatch(B2:B,"(?i)bp"),ifs(C2:C="","",bp,"",true,byrow(A2:C,
lambda(r,let(uniq,UNIQUE(filter({A2:A,C2:C},C2:C<>"",not(bp),A2:A=index(r,1))),
xmatch(1,(index(uniq,0,1)=index(r,1))*(index(uniq,0,2)=index(r,3)))))))))

enter image description here

You could select individual columns maybe like this:

=ArrayFormula(let(bp,regexmatch(B2:B,"(?i)bp"),ifs(C2:C="","",bp,"",true,byrow({A2:A,C2:C},
lambda(r,let(uniq,UNIQUE(filter({A2:A,C2:C},C2:C<>"",not(bp),A2:A=index(r,1))),
xmatch(1,(index(uniq,0,1)=index(r,1))*(index(uniq,0,2)=index(r,2)))))))))

Not sure about the second point. You could include a header like this:

=ArrayFormula({"SR No";let(bp,regexmatch(B2:B,"(?i)bp"),ifs(C2:C="","",bp,"",true,byrow({A2:A,C2:C},
lambda(r,let(uniq,UNIQUE(filter({A2:A,C2:C},C2:C<>"",not(bp),A2:A=index(r,1))),
xmatch(1,(index(uniq,0,1)=index(r,1))*(index(uniq,0,2)=index(r,2))))))))})

and it is true that if you inserted cells above the data in cols A-D then A2 in the formula would change to A3 etc.?

Try this version as suggested in the comment:

=ArrayFormula({"SR No";let(rcount,counta(tocol($A:$A,1)),name,index($A:$A,2):index($A:$A,rcount),
qual,index($B:$B,2):index($B:$B,rcount),cat,index($C:$C,2):index($C:$C,rcount),bp,regexmatch(qual,"(?i)bp"),
ifs(cat="","",bp,"",true,byrow({name,cat},
lambda(r,let(uniq,UNIQUE(filter({name,cat},cat<>"",not(bp),name=index(r,1))),
xmatch(1,(index(uniq,0,1)=index(r,1))*(index(uniq,0,2)=index(r,2))))))))})

Upvotes: 1

rockinfreakshow
rockinfreakshow

Reputation: 30281

You may try:

=map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,let(Λ,offset(Σ,,2),if(or(ifna(xmatch("*bp*",offset(Σ,,1),2)),Λ=""),,
 let(z,filter(C:C,A:A=Σ,C:C<>"",not(regexmatch(B:B&"","(?i)bp"))),y,unique(z),x,{y,sequence(rows(y))},vlookup(Λ,x,2,))))))

enter image description here

Upvotes: 2

Related Questions