Extracting data/numerical values from a give list
 

On Fri, 1 Dec 2006 05:05:01 -0800, Dee wrote:

Need help with the following:
I have a given list of 5 digit random numbers starting with
00001 and ending with 99999
I need to be able to review my list example below:
10211
10219
11040
11089
and figure out from 00001 to 99999 what numbers are missing from the
beginning and end of the data stream. This seems easy - 00001 to 10210 -
then start up again at 11090 to 99999. The problem occurs when I try to pull
in all the missing data between 10211 and 10219. I need to be able to set a
criteria then extract all the data from a given set of numbers, which will
create a new list of numbers. This is not a process that can be done
manually due to the size of the main data source (50,000 lines of code)

Please help............

Brute force?

==========================
Option Explicit

Sub MissingNumbers()
Dim i As Long, j As Long
Const LowerLimit As Long = 1
Const UpperLimit As Long = 99999
Dim List As Range
Dim Output As Range

Set List = Range("A2:A100")
Set Output = Range("B1")

j = 0
For i = LowerLimit To UpperLimit
If Application.WorksheetFunction.CountIf(List, i) = 0 Then
j = j + 1
Output.Offset(j, 0).Value = i
End If
Next i

End Sub
=============================


--ron

Extracting data/numerical values from a give list
 

On Fri, 1 Dec 2006 07:36:00 -0800, Dee wrote:

Hello Ron,
Thanks for the infro - I am still a little new in the coding area, but I was
able to edit the VB and tighten it up so my out put didnt exceed the excel
sheet.

Here's another one for you:
Now I have a list of codes where
10122
10123
10124
10125
10126
and I need to place them in code span arrangement. Example my output list
in column B is now listing all codes that are not found in the main search,
but I need to list them in a span 10122 - 10126, then if there is another
code 10128 that falls out of my range list it alone. place that code alone
10128 - 10128, then continue parsing through my list to find all numbers that
fit within a range and list them accordingly. 10130 - 10250
Example:
10122 - 10126
10128 - 10128
10130 - 10250
If you noticed 10127 and 10129 are not present in my list - that is due to
their main origin in list number. List number two - pulled them out via the
criteria set in your declaration.

Thank you




It's just a matter of using appropriate loops to check all the values.

What is this for?


=============================
..
..
..
'set up ranges
lSpan = LowerLimit
j = 0
For i = LowerLimit To UpperLimit
Do While Application.WorksheetFunction.CountIf(List, i) = 0
i = i + 1
If i UpperLimit Then Exit Do
Loop
j = j + 1
sOutput.Offset(j, 0).Value = lSpan & " - " & i - 1
Do Until Application.WorksheetFunction.CountIf(List, i) = 0
i = i + 1
Loop
lSpan = i
Next i
..
..
..
================================
--ron