CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Maximilian Hallett
Maximilian Hallett

Reputation: 23

How to iterate over a countif checker in an if function

Im not an expert at excel/sheets. I wanted to make a dynamic function that would stop user error by doing all of the work. The function basically uses regex to get the first letters and adds a number i.e. stell ball = sb01, stell cage = sc01 and so on. My problem grows when I also want to be able to catch multiple entries and potentially offer the next iteration. i.e. stell ball = sb01, stell ball = sb02.

Here is my code ive got working so far:

=COUNTIF(B:B,B1)>1 + CONCAT(ArrayFormula(REGEXREPLACE(proper(A1:A),"[^A-Z]+","")),RIGHT("00"&ROW(A:A),2))

Im getting bogged down with how to iterate the number. Im thinking it needs to be an IF function but Im getting into a super nested problem. In my mind it should be something on the lines of:

=IF(COUNTIF(B:B,B1)>1 + CONCAT(ArrayFormula(REGEXREPLACE(proper(A1:A),"[^A-Z]+","")),RIGHT("00"&ROW(A:A),2)),CONCAT(ArrayFormula(REGEXREPLACE(proper(A1:A),"[^A-Z]+","")),RIGHT("00"&ROW(A:A),+1),"")

Upvotes: 0

Views: 115

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A1:A="",,
 REGEXREPLACE(PROPER(A1:A), "[^A-Z]+", )&
 TEXT(COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A)), "00")))

enter image description here

=ARRAYFORMULA(IF(A1:A="",,
 REGEXREPLACE(PROPER(A1:A), "[^A-Z]+", )&
 TEXT(COUNTIFS(REGEXREPLACE(PROPER(A1:A), "[^A-Z]+", ), 
 REGEXREPLACE(PROPER(A1:A), "[^A-Z]+", ), ROW(A1:A), "<="&ROW(A1:A)), "00")))

enter image description here

Upvotes: 2

Related Questions