I want to create an auto-number for each row in my google sheet following the logic below:
I have the formula that I can drag down from A2 and all works fine, but whenever I add a row the formula doesnt get copied into it, so i want to create an ARRAYFORMULA to fill the whole row according to the conditions above. This is my formula in A2 that I can drag down and works:
=IF(B2 = "", "", IF(C2 = "x QTY", IF(INT(MAX($A$1: $A1)) <= $A$1, $A$1 * 10, INT(MAX($A$1: $A1)) + 1), MAX($A$1: $A1) + 0.01))
I have tried to transpose this to the following ARRAYFORMULA but it just does not increment the way I want:
=ARRAYFORMULA(IF(B2:B = "", "", IF(C2:C = "x QTY", IF(INT(MAX(A$1:INDEX(A:A, ROW()-1))) <= $A$1, $A$1 * 10, INT(MAX(A$1:INDEX(A:A, ROW()-1))) + 1), MAX(A$1:INDEX(A:A, ROW()-1)) + 0.01)))
Anyone know why this is not working? I am in Google Sheets and not Excel. Thanks for your help
10.00 | New Table - Rename | x QTY |
---|---|---|
10.01 | item 1 | 1 |
(empty) | ||
10.02 | item | 1 |
11.00 | New Table - Rename | x QTY |
(empty) | ||
11.01 | Item 2 | 1 |
11.02 | Item 3 | 1 |
12.00 | New Table - Rename | x QTY |
12.01 | Item 1 | 1 |
12.02 | Item 2 | 1 |
12.03 | Item 3 | 1 |
Here's one approach you may test out; Cell_A1
is marked 1 here:
=map(C2:C,lambda(Σ,if(Σ="",,let(Λ,"x QTY",countif(C2:Σ,Λ)-1+A1*10 + if(Σ<>Λ,(counta(xlookup(Λ,C2:Σ,C2:Σ,,,-1):Σ)-1)*0.01,)))))