Hello,
The attached macro, which finds the missing, numbers from 1 to 30. I got it from under this link...
For example in the column "A" there are some random, macro find missing numbers in range 1 to 30 and list them in the column B As show in the example below with the sheet 1.
Example Sheet1
Please I need help to modify the missing numbers macro.
1-Which can find missing number from the column "A" and list them in to column "E"
2- can find missing number from the column "B" and list them in to column "F"
3- can find missing number from the column "C" and list them in to column "G"
As show in the example below with the sheet 2.
Example Sheet2
Thank you all.
I am using Excel 2000
Regards,
Moti
The attached macro, which finds the missing, numbers from 1 to 30. I got it from under this link...
Find missing numbers from random numbers list
I have a huge list of numbers which consists of random numbers from 1 to 100000 like 1,2,5,6 etc. I want to find out the numbers which are not in the list, i.e. 3,4 in above example. How can I do it?
www.mrexcel.com
VBA Code:
Option Explicit
Sub MissingValues()
Const MAX_NUMBERS = 30 ' 100000 ' Maxmimum value expected
Const MAX_ROWS = 30 ' 3000 ' Number of rows in output list
' before using next column
Dim blnNumbers(1 To MAX_NUMBERS) As Boolean
Dim lngRow As Long
Dim rngData As Range
Dim lngIndex As Long
Dim intCol As Integer
' mark all existing numbers as true
For Each rngData In Range(Range("A1"), Range("A65536").End(xlUp))
blnNumbers(rngData.Value) = True
Next
' output list(s) of missing values
intCol = 2
For lngIndex = 1 To MAX_NUMBERS
If Not blnNumbers(lngIndex) Then
lngRow = lngRow + 1
Cells(lngRow, intCol) = lngIndex
If lngRow = MAX_ROWS Then
lngRow = 0
intCol = intCol + 1
If intCol > 256 Then
MsgBox "No more space", vbExclamation
Exit Sub
End If
End If
End If
Next
End Sub
For example in the column "A" there are some random, macro find missing numbers in range 1 to 30 and list them in the column B As show in the example below with the sheet 1.
Example Sheet1
1 | 4 |
2 | 5 |
3 | 6 |
10 | 7 |
11 | 8 |
12 | 9 |
13 | 15 |
14 | 16 |
22 | 17 |
26 | 18 |
19 | |
20 | |
21 | |
23 | |
24 | |
25 | |
27 | |
28 | |
29 | |
30 |
Please I need help to modify the missing numbers macro.
1-Which can find missing number from the column "A" and list them in to column "E"
2- can find missing number from the column "B" and list them in to column "F"
3- can find missing number from the column "C" and list them in to column "G"
As show in the example below with the sheet 2.
Example Sheet2
1 | 22 | 27 | 4 | 4 | 6 | |
2 | 22 | 29 | 5 | 6 | 7 | |
3 | 27 | 29 | 6 | 7 | 9 | |
10 | 11 | 4 | 7 | 8 | 15 | |
11 | 5 | 4 | 8 | 9 | 16 | |
12 | 25 | 12 | 9 | 15 | 17 | |
13 | 22 | 22 | 15 | 16 | 18 | |
14 | 19 | 24 | 16 | 17 | 20 | |
22 | 5 | 19 | 17 | 18 | 21 | |
30 | 14 | 8 | 18 | 20 | 23 | |
10 | 4 | 19 | 21 | 26 | ||
6 | 7 | 20 | 23 | 28 | ||
25 | 21 | 24 | ||||
14 | 23 | 26 | ||||
29 | 24 | 28 | ||||
25 | 29 | |||||
26 | ||||||
27 | ||||||
28 | ||||||
29 |
Thank you all.
I am using Excel 2000
Regards,
Moti