[Resolved]]Excel formula - ignore zeros

[Pirate]

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

[Siddharth Rout]

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...

[Pirate]

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.

[Siddharth Rout]

Oh Ok....

like this?

1
2
3
4
0
5
6
7
8
9
10

[Pirate]

yes exactly

[Siddharth Rout]

Would this help?

[Pirate]

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.

[Siddharth Rout]

I have updated the attachment above...

[anhn]

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.

[Pirate]

koolsid , yes that did the trick ...but i was looking for non-code solution...thanks a lot though

[Pirate]

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.

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 !!!

[Siddharth Rout]

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...

[Pirate]

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 ??

[Siddharth Rout]

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...

[Pirate]

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?

[Siddharth Rout]

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?

[anhn]

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 !!!

You did it wrong way!
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:

           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)

[Pirate]

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?

Try to put at least 2 consecutive zeros then you will see it doesn't work !

[Pirate]

You did it wrong way!
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:

           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)

Ok it does work..thank but i couldn't apply this formula on existing column! possible ?

[anhn]

What column? What is the first cell?

[Pirate]

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 A1

btw, what does $ stands for (1+MAX(A$2:A2)

[anhn]

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.

[Pirate]

alright..it's working but last question , is there any way other than pasting the formula to non zero fields ???

[anhn]

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.

[anhn]

This is a simplest function, it can be linked to a button.

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

[Pirate]

Thanks anhn...the function is working