Formula for identify missing numbers from a given range.

[Noorking]

Hi All,

Does anyone know how to use a formula to identify the MISSING numbers from the range smallest to largest (given below the range 1640-1875)? For eg:-

Column A(range 1640-1875) Column B - Here it should display the missing numbers from the given
range
1646
1643
1648
1649
1641
1790
1796
1805
1804
1807
1810
1863
1875

Regards

[Richard Buttrey]

Hi,

There are no doubt many ways. A simple and pragmatic non VBA way is the following.

List the numbers 1640-1875 in a spare column say C1:C236
Then in B1 enter

Please Login or Register  to view this content.

Copy this down to B236

[Ali Kırksekiz]

B2 = 1640 (Min value)
B3 = 1875 (Max value)

B4 cell

Please Login or Register  to view this content.

C2 cell

Please Login or Register  to view this content.

This is an Array Formula (C2 cell formula) 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.

Then drag down.

[samba_ravi]

See the attached file

[samba_ravi]

Ali Kırksekiz inspired from @ Ali Kırksekiz


can try the below formula in cell B1 in the attached file in Post No 4
=IFERROR(ROW(INDEX(A:A,SMALL(INDEX(ISNUMBER(MATCH(ROW(INDIRECT($D$1&":"&$D$2-1)),A$1:A$13,0))*10^10+ROW(INDIRECT($D$1&":"&$D$2-1)),0),ROW(A1)))),"")

[Noorking]

Thank you for the reply.

Actually I am not so good with excel. Would be highly obliged if you could please explain me the formula given in the attached document provided by you (Missing Numbers).

Reason being I just don't want to copy paste the formula, I really want to understand it first and then work with it.

Regards