Printable View
I have like this data below :
1
2
3
4
0
6
7
8
9
10
simply i need to completey ignore any zero and continue on from the above number .eg.:
based on above data, after the zero , number 6 should be 5 (i have many zeros btw).
Is it easy in excel ??
Thanks
If the 1st two numbers are already populated and the numbers are in sequence then you don't need a formula or code... Simply highlight the first two cell and use the auto fill feature...
That will not ignore the zero and will replace it with next sequence number .. I need all zero to be untouched and the next number after zero to continue off the above the zero cell ...if it's not clear , let me know to explain it differently.
Oh Ok....
like this?
1
2
3
4
0
5
6
7
8
9
10
yes exactly
Would this help?
The button inside the sheet is not clickable..i tried to d-click it but it shows code view..is there any shortcut or something?..I have excel 2007 btw.
I have updated the attachment above...
You can do it this way without VBA code:
1. In cell A2, enter: 1
2. In cell A3, enter: =MAX(A$2:A2)+1
3. Copy the formula down to, let say, cell A50
4. Now you have a sequence from 1 to 49
5. Enter 0 to wherever you want to overwrite a formula and you will see the values auto updated.
koolsid , yes that did the trick ...but i was looking for non-code solution...thanks a lot though
I think this one is working except it's duplicating numbers twice like [11,11,12,12..etc] ..I don't know what's wrong although i did exactly like above..any way to get rid of dublicates !!!Quote:
Originally Posted by anhn
Ok, Here is another version of it which you can modify to suit your needs...
Remember Data has to start from 3rd row else it will give you error...
This is a very basic version but does what you want and can be further amended to what you want...
Hope this helps...
I don't know if i did it right but it's not working..i did like this :
selected the first top two cells and dragged little button right corner all the way to the bottom ..is this the correct way ??
The formula is already there....all you need to do is fill the first column and if it goes down then select the last cell in the 2nd column and drag the formula down...
Yeah..it's working but
1.can't i just copy the formula and apply it on the autonumber that i have without creating extra column?
2.If i have two consecutive zeros , the code breaks so any solution?
For that you have to tweak the formula that I gave you but it will become more complex... I suggest using Anhn's way... BTW what problems were you facing with it? It is working perfectly for me?
You did it wrong way!Quote:
Originally Posted by Pirate
After enter the formula in cell A3, you should copy only that single cell (A3) down instead of copy both cells (A2:A3).
The formulas should be like this:
Code:A .
1
2 1
3 =1+MAX(A$2:A2)
4 =1+MAX(A$2:A3)
5 =1+MAX(A$2:A4)
6 0
7 =1+MAX(A$2:A6)
8 =1+MAX(A$2:A7)
9 =1+MAX(A$2:A8)
10 =1+MAX(A$2:A9)
11 =1+MAX(A$2:A10)
12 =1+MAX(A$2:A11)
13 =1+MAX(A$2:A12)
14 0
15 =1+MAX(A$2:A14)
16 =1+MAX(A$2:A15)
17 =1+MAX(A$2:A16)
18 =1+MAX(A$2:A17)
19 0
20 =1+MAX(A$2:A19)
21 =1+MAX(A$2:A20)
Try to put at least 2 consecutive zeros then you will see it doesn't work !Quote:
Originally Posted by koolsid
Ok it does work..thank but i couldn't apply this formula on existing column! possible ?Quote:
Originally Posted by anhn
What column? What is the first cell?
The sheet that I have has ID column (autonumber) with some zeros in and the first cell is A1Quote:
Originally Posted by anhn
btw, what does $ stands for (1+MAX(A$2:A2)
When you say the first cell is A1, I assume A1 contains a number, not the column header "ID".
1. In A2, enter: =1+MAX(A$1:A1)
2. Copy cell A2
3. Paste (formula) to all non-0 cells
The formulas will auto adjust to such as in A3: =1+MAX(A$1:A2)
$ is used for absolute row. In this case $1 (row 1) will be kept unchange.
alright..it's working but last question , is there any way other than pasting the formula to non zero fields ???
Use VBA code to do the job of creating formula, copy and paste to non-zero cells for you.Quote:
Originally Posted by Pirate
This is a simplest function, it can be linked to a button.
Code:Function FillNonZeroSeq()
Dim n As Long, r As Long
With Selection '-- select a range then run this function to fill
n = Val(.Cells(1))
For r = 2 To .Rows.Count
If .Cells(r, 1) <> "0" Then
n = n + 1
.Cells(r, 1) = n
End If
Next
End With
End Function
Thanks anhn...the function is working :)