Fill in missing sequential increment.

[jeff p]

Hello..

I have data in several rows whereby my column D looks like this:

FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000

Throughout the week, additional data is added to the rows, thus creating blank cells within this D Column:

FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000

FebtReport14_04.0000

FebtReport14_05.0000

Is there a macro i can run which will re increment only the blank cells to look something like this?"

FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_03.1000
FebtReport14_04.0000
FebtReport14_04.1000
FebtReport14_04.2000
FebtReport14_04.3000
FebtReport14_05.0000

Also , I will be adding new data quite often, in which newer rows may need to be added in between: ie:

from this

FebtReport14_04.0000


FebtReport14_04.1000

to something like this:

FebtReport14_04.0000
FebtReport14_04.0100
FebtReport14_04.0200
FebtReport14_04.1000

Any ideas how to achieve something similar to this?
Thanks!

[Kaper]

So main point is to compare suffix and namely first available zero position in predcessor and successor.
Try below code (of course one shall add checking of first empty row and also include special treatment if more than 9 rows were added):

Please Login or Register  to view this content.

Run the code and examine yellow cells in attachment.

[jeff p]

Thank you very much, Kaper!

This code works great!
I really appreciate the included xls file!

..Is it possible to slightly modify this?... such that every new row/s that's added, would add the numerical value that is higher than all the rows above, and lower than all rows below, thus maintaining its Ascending sorting order?

Currently
When a new row is added between cells 01. 000 and 02.000, the script adjusts it to 01.100 ( which works great)
When a new row is added between cells 01. 000 and 01.100, the script adjusts it to 01.010 ( which also works!)
However
If a row is added between 01. 000 and 01.010 the script adjusts it to 01.100 ( which is no longer in the correct sorting order)

Thanks again for the help!

Jeff

[Kaper]

Hi Jeff,

Can you try to write it in a more "a cooking reciepe" (algoritmic) words, not just sample by sample?

Anyway, I made another attempt, but if this not right one - see above.

This part could be the same:

Please Login or Register  to view this content.

At this point we know what preccessor was and successor is, and how many empty cells are between.
So we need to determine where to put our sequence 1 2 ... j
in previous code it was max of 0 position in successor and predcessor

you wrote:
If a row is added between 01. 000 and 01.010 the script adjusts it to 01.100 ( which is no longer in the correct sorting order)
but gave even no clue what would be the correct one.

May be the following would properly find a place to insert new sub-series:

Please Login or Register  to view this content.

and final part untouched:

Please Login or Register  to view this content.

[jeff p]

Thank you very much , Kaper!
I really appreciate the time you've taken with this
..
I also apologize for my inept attempt at conveying my request, as Its difficult to put into Algorithmic terms what I'm looking for, with my feeble logistical skills.

In my previous post, the sorting order I was referring to was to use the Sort and Filter "Sort by A to Z",
After inserting new blank rows and running your script, I would select the D column and performing this sorting filter, whereby every row should maintain their row position.
This process is done several times to test. ie Add blank rows, run the script, run the sorting filter, add more rows, run the script ,then use the filter.. etc..

Another way to explain my initial request:
All blank rows must insert an incremental value between the cell below and above value, while maintaining its ascending order. . All rows should have their own unique numerical value.

It may not be possible to achieve this with my initial example of inserting values of 01.100, 01.200, 01.300.
... Perhaps if every new entry is exactly half of the above and below cell value?
As an example: With a blank row added between 01.000, and 02.000, and running the script would yield a value of 01.500
if 3 blank rows were added between 03.000, and 04.00. After running the script, those blank values could be displayed as 03.250, 03.500, 03.750, respectively.
This is just another possible approach. ( albeit may not very efficient)

I realize that with only three digits, inserting new rows several times will eventually run out of digit placeholders , but i may only need to perform this a dozen times.

..sorry for any confusion...

Thanks again Kaper!

Jeff

Jeff

[Kaper]

Well, I think that the above code just to make sure below is whole one) works as described. The issue with adding new items - yes with the approach specified - you are limited by number of digits after decimal period. 3/4 (started with 4, while last posts you mention 3) does not matter much.
If you need more "adding actions accomodated - probably different approach is needed - you shall consider updating also existing numbers - so general re-numbering.
But this is quite different issue so think we shall close this thread.

Please Login or Register  to view this content.