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.

Raskyl's avatar
Raskyl
Copper Contributor
Jun 04, 2018
Thanks SO MUCH Sergei!! I was wondering if you could explain how it works and why you chose what you chose so i know how to use them in the future?

But still i really thank you from the bottom of my heart!!
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 04, 2018

Hi Raskyl,

 

The idea is to find latest non-blank cell in first column before current and based on its value and current row number generate log record in second column.

 

We use AGGREGATE https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df to return the row number for the last non-blank cell in first column. I n row 5 it'll be like

=AGGREGATE(14,6,1/NOT(ISBLANK($A$2:A5))*ROW($A$2:A5),1)

First parameter means return the largest value in the array, second says what we ignore all errors and the rest is the array itself. Latest 1 means we take first largest value. Within array we have a filter

1/NOT(ISBLANK($A$2:A5))

which returns error if the cell in A is blank and 1 otherwise, if evaluate the formula it'll be like

=AGGREGATE(14,6,{1;#DIV/0!;#DIV/0!;#DIV/0!}*{2;3;4;5},1)

or multiplying two arrays on each other

=AGGREGATE(14,6,{2;#DIV/0!;#DIV/0!;#DIV/0!},1)

Since we ignore all errors the formula returns 2 - that's the row number for the latest cell where we have the text in column A.

 

Using

=OFFSET($A$1,[@Aggregate]-1,0)

we return the text itself and parse it taking the most right letter in it.

On the another part of the main formula we calculate the difference between current row number and header, i.e. row number which returns us AGGREGATE

=[@Row]-[@Aggregate]

After all we combine all together to generate log record.

 

I split part of the formula into separate columns in attached file.

  • Raskyl's avatar
    Raskyl
    Copper Contributor
    Jun 06, 2018
    Thanks again Sergei!

    I’m trying to see if i can make an if then statement now with offset and aggregate to count how many test cases i have in each section. And i probably still don’t understand it well enough. Thanks a lot for your help man!! :3 i appreciate it!

Resources