Forum Discussion
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_SoBronze Contributor
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: