Find missing numbers & range from list of numbers

S

samshan143

New Member
Joined
Mar 12, 2013
Messages
23
Hi,

I have a data with numbers, which ideally should in series of 50s (i.e. 250-300 or 35000-35050, etc etc). I have attached the sample sheet of numbers,

[TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]205[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]206[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]207[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]208[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]210[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]211[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]213[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]214[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]216[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]217[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]223[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]224[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]229[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]230[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]233[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]243[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]244[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]245[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]249[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]251[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]259[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]260[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]261[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]262[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]263[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]264[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]265[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]267[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]268[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]271[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]272[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]273[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]274[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]275[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]279[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]282[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]283[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]284[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]285[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]286[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]287[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]289[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]290[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]292[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]293[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]294[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]295[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]300[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]301[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]304[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]305[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]308[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]310[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]325[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]334[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]337[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]339[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]340[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]341[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]342[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]347[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]348[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]350[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]352[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]353[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]354[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]355[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]357[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]358[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]359[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]362[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]363[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]364[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]365[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]366[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]367[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]371[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]372[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]375[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]378[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]379[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]380[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]382[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]383[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]384[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]386[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]399[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]400[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]401[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]402[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]405[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]407[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]408[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]409[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]428[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]429[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]430[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]431[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]435[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]436[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

what result i expect is that,

1) which numbers are missing
2) what is the range of this missing numbers in the series of 50s (i.e. suppose missing number is 65 then range shuld be 50-100)

pls guide.

Sam
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Give this macro a try...
Rich (BB code): 
Sub ListMissingNumbersAndRange()
  Dim X As Long, Z As Long, LastRow As Long, StartNumber As Long
  Dim Missing As String, Numbers As Variant, NumberRanges() As String
  Const OutputStartCell As String = "B1"
  Const DataCol As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  For X = StartRow + 1 To LastRow
    If Cells(X, DataCol).Value <> Cells(X - 1, DataCol).Value - 1 Then
      For Z = Cells(X - 1, DataCol).Value + 1 To Cells(X, DataCol).Value - 1
        Missing = Missing & " " & Z
      Next
    End If
  Next
  Numbers = Split(Trim(Missing))
  ReDim NumberRanges(0 To UBound(Numbers))
  For X = 0 To UBound(Numbers)
    StartNumber = Numbers(X) - (Numbers(X) Mod 50)
    NumberRanges(X) = StartNumber & " - " & StartNumber + 49
  Next
  Range(OutputStartCell).Resize(UBound(Numbers) + 1) = WorksheetFunction.Transpose(Numbers)
  Range(OutputStartCell).Offset(, 1).Resize(UBound(Numbers) + 1) = WorksheetFunction.Transpose(NumberRanges)
End Sub
Ther e are some constants (the Const statements) that let you control where the data is and where you want the output to go to... adjust them as needed by your setup. Also note that I did not set your ranges like this... 50-100... because doing so would make the end of one range the same as the beginning of the next mean numbers like 100 would belong to two ranges, so I set the end of each range to one less than the beginning of the next (that is, for example, 50-99). If you really want the ranges to overlap as your original message indicated, then change the 49 that I highlighted in red to 50.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
280
Fluff
Fluff
S
  • Solved
Find the missing numbers from the range of numbers and filling the missing number in the cells below the last row
Replies
6
Views
606
sekar
S
G
  • Question Question
The invoice number from the list
Replies
5
Views
370
Peter_SSs
Peter_SSs
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
300
Peter_SSs
Peter_SSs
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff

Forum statistics

Threads
1,224,889
Messages
6,181,605
Members
453,055
Latest member
cope7895

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top