Urgent: Missing numbers formula please

[naaim]

I want to get all the missing numbers from Column A to Column B.

For an example:
V7rYg.png

My numbers in Column A starts with 0.01 and it goes on like 0.01, 0.02 0.03 (add 0.01 to each) and it goes on upto 100-150. But some numbers are missing and I want them all in column B. Someone please help me get a formula for this.


Thanks.

[Glenn Kennedy]

Here's a possible solution (suggested by shg on this forum, a couple of months back)...

=IFERROR(IF($A2 > A1+1, A1+1, ""), "")

Copy down & across (changing the 1 to 0.01 in your real sheet).

[boopathiraja]

Hi,

B2=

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Ref the attachment...

[naaim]

HI,

boopathiraja's formula worked but it only get missing numbers upto 0.20

numbers in column A goes upto 100 and above.

thanks for the help.

[naaim]

It worked thanks

[boopathiraja]

Hi, Try this (will take some time for process )

Please Login or Register  to view this content.

[Glenn Kennedy]

Rep addded to Boopathiraja. However, I'd like to suggest an enhancement. His solution keeps on adding "missing" values, after the maximum value in column A has been reached. This one stops once the maximum is reached.

Otherwise, it's the same elegant solution.