google-sheetsgoogle-apps-scriptgoogle-sheets-formulaspreadsheet

Auto numbering rows in GoogleSheet


I want to create an auto-number for each row in my google sheet following the logic below:

  1. it takes the sheet number from A1 e.g. 8
  2. generates sequential table header numbers from that sheet number for each table header row e.g. first table 80.00 then 81.00 then 82.00 etc
  3. within each table the line items should be 80.01 then 80.02 then 80.03 etc for table 80.00 and 81.01 then 81.02 etc for table 81.00
  4. the numbers should not increment whenever the cell on the same row in B is blank
  5. to detect a table header we have a recurring value in column C that is constant for table headers and is "x QTY"
  6. the numbers should automatically update when users insert values and/or rows and/or delete values in column B

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

Solution

  • 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,)))))
    

    enter image description here