Forum Discussion
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 to this one table and the only way i can do so is by a cumbersome method but this method is flawed as the sections inbetween will also be resequenced to the wrong “Log entry”.
My code is :
=IF(AND(ROW()>60,ROW()<81),$S$3&TEXT(ROW()-61,"00"), IF(AND(ROW()>80,ROW()<101),$S$4&TEXT(ROW()-81,"00"), IF(AND(ROW()>100,ROW()<140),$S$5&TEXT(ROW()-101,"00"),$S$2&TEXT(ROW()-2,"00"))))
The $S$2/3/4/5 is a label im using eg: ROW S2= “LOG_testA_”, ROW S3=“LOG_TestB_”....
So if i add a new cell, i have to renumber the “AND” row. That’s the cumbersome thing. Is there something i can do to overcome this? something to read from a column or row? seems like some far-fetched kind of code.
i used a Excel 2010 and uploaded it to google drive. so if anyone can be so kind as to help, i'd be so appreciative!
https://docs.google.com/spreadsheets/d/1A9DWjcIARxskxTvyKppX01NQHwycpP2jAwt8C3wGsz8/edit?usp=sharing
Edit: i noticed downloading it in excel 2016 will take the table i've created away.
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.
4 Replies
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.
- 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!!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.
