Forum Discussion

Raskyl's avatar
Raskyl
Copper Contributor
May 31, 2018
Solved

auto count up in a column with a label and start new count and label when row has new label

I have typed a self counting up code in a table to help with the issue i had previously where the labeled numbering system would count up even when i add cells in between. I have multiple sections t...
Formulas & Functions
Requests
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 03, 2018

    Hi Raskyl,

     

    If structure of the data as in your sample you may use for numbering

    =IF(ISBLANK($A3),
   "Log_Test" &
      RIGHT(OFFSET($A$1,AGGREGATE(14,6,1/NOT(ISBLANK($A$2:A3))*ROW($A$2:A3),1)-1,0),1) &
      "_" & TEXT(ROW()-AGGREGATE(14,6,1/NOT(ISBLANK($A$2:A3))*ROW($A$2:A3),1),"00"),
 "")

    where AGGREGATE finds the latest text in column A.

     

    Please see attached - Sheet1 with range, Sheet2 with table.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 03, 2018

Hi Raskyl,

 

If structure of the data as in your sample you may use for numbering

=IF(ISBLANK($A3),
   "Log_Test" &
      RIGHT(OFFSET($A$1,AGGREGATE(14,6,1/NOT(ISBLANK($A$2:A3))*ROW($A$2:A3),1)-1,0),1) &
      "_" & TEXT(ROW()-AGGREGATE(14,6,1/NOT(ISBLANK($A$2:A3))*ROW($A$2:A3),1),"00"),
 "")

where AGGREGATE finds the latest text in column A.

 

Please see attached - Sheet1 with range, Sheet2 with table.

Resources