I'll have a list of numbers, each on its own line (say 0 -100). How do I find the numbers that are not listed or missing?
In a blank cell, enter the formula of =IF(A3-A2=1,"","Missing"), and press the Enter key. In this case, we enter the formula in Cell B2. If there is no missing numbers, this formula will return nothing; if missing numbers exist, it will return the text of "Missing" in active cell.
Add them all to a set. Then subtract from a set filled with 1-100. Here's an example for 0-9:
>>> set(range(10)) - set([1, 4, 5, 6, 8, 2])
set([0, 9, 3, 7])
>>>
I had [1, 4, 5, 6, 8, 2]
listed. To find out which numbers in range 0-9 are missing, I created a set with all of 0-9 and then subtracted the set with [1, 4, 5, 6, 8, 2]
from it. And found out that [0, 9, 3, 7]
were missing.
Sets are fairly efficient for this. As an added benefit, duplicates will be handled gracefully.
If L is the list of numbers, then
set(L).difference(xrange(101))
saves creating a set from the xrange
In [1]: L=[1, 4, 5, 6, 8, 2]
In [2]: timeit set(range(101)) - set(L)
10000 loops, best of 3: 21.7 µs per loop
In [3]: timeit set(L).symmetric_difference(range(101))
100000 loops, best of 3: 14.2 µs per loop
In [4]: timeit set(L).difference(range(101))
100000 loops, best of 3: 9.73 µs per loop
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With