Forum Discussion

lpezanko's avatar
lpezanko
Copper Contributor
Jul 19, 2021

How to automate addition of zeros in a column for non-sequential numbers: SEE FULL EXPLANATION BELOW

Step 1:

I have a column, K, in which there should be numbers sequentially orders starting from zero. In some cases there will be missing numbers: i.e. 129, 130, 133 thus two additional zeros must be added between 130 and 133 as such: 130, 0, 0, 133. This would be in a column orientation and thus reading downward. 

 

A file will be attached providing an example of the dataset used. Highlighted in green contains an example of a manual modification representing the aforementioned example. I need help automating this function throughout the entire column.

 

Step 2:

Sort column K & J by column K by smallest to largest

 

Step 3:

In column J, all values contains brackets needs to be removed: i.e. [1] converted to 1, [2] to 2 etc.

 

Step 4:

Save file as .txt file with current filename.

 

Thoughts on running a script/macro for step 1:

My assumption is that this might be written by determining the difference between each cell and then adding x zeros if the difference is > 1;

i.e. if difference is 2 add 1 zero,

if difference is 3 add 2 zeros

if difference is 4 add 3 zeros...

 

The issue is that when reaching zero compared to the next number, this comparison should be skipped, thus, a command; if number is 0, move to next number should be indicated. 

 

The most difficult part of this task is the first step so It would be incredibly appreciative if I could receive some input or help on how to achieve this. Please message me if there are any questions. 

 

Thank you to anyone who provides some feedback!

Luke

1 Reply

  • Yea_So's avatar
    Yea_So
    Bronze Contributor

    lpezanko 

     

    Step 1:

    Step2:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J40,CHAR(32),),"[",),"]",)

    Copy and paste as values to Column [J]

    Step3: I was not sure about the sorting instruction so I skipped that step

    Step4: Save file as .txt file with current filename. Skipped that step (you do it)

    See file attached: