Identify missing record numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a work sheet that has sheet numbers from 1 to 2800. I now need to
identify those sheet numbers that have been deleted. See the example below.

Is there a formula for this?

Sheet Project Name
Number
1 Abundant Life Christian Center
2 Abundant Life Christian Center
3 Abundant Life Christian Center
6 Admiral Byrd Drive
7 Admiral Byrd Drive
8 Admiral Byrd Drive
10 Aerocenter Lot 4
11 Aerocenter Lot 4
12 Aerocenter Lot 4
14 Airport Warehousing at Arbor Court
15 Airport Warehousing at Arbor Court


TIA
 
Hi!

One way based on your small sample:

Assume the "consecutive" numbers are in column A, A1:A11

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(ROW($1:$15),SMALL(IF(COUNTIF(A$1:A$11,ROW($1:$15))=0,ROW($1:$15)),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

Based on your sample, will return:

4
5
9
13
#NUM!

ROW($1:$15) represents the numeric range of the posted sample. If you
actually have 1:2800 then change ROW($1:$15) to ROW($1:$2800)

Biff
 
The 'sheets' are scanned drawing files that we catalog by a sheet number
label attached to the drawings.
 
Back
Top