Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
This an example of part of my list:
050000698 050000703 How do I get a list of the numbers that are NOT in that list? For example: 050000699 050000700 050000701 050000702 Note: My entire list contains numbers from 050000698 to 051003040 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
GWC wrote:
This an example of part of my list: 050000698 050000703 How do I get a list of the numbers that are NOT in that list? For example: 050000699 050000700 050000701 050000702 Note: My entire list contains numbers from 050000698 to 051003040 Where will the results go? A series of cells, a VBA array, something else? -- You had an affair in the 90 seconds I was gone?! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
Auric__ has brought this to us :
GWC wrote: This an example of part of my list: 050000698 050000703 How do I get a list of the numbers that are NOT in that list? For example: 050000699 050000700 050000701 050000702 Note: My entire list contains numbers from 050000698 to 051003040 Where will the results go? A series of cells, a VBA array, something else? I was thinking to dump the list into an array and iterate that for non-consecutive numbers. When not found consecutive, subtract previous element from current element and insert into a new 1 dim array. Then dump the new array back into the wks where specified. Only thing is making time to do it! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
Gary,
Try this and let me know if it does what you want... Option Explicit Sub GetMissingNumbers() Dim n&, k&, x&, vDataIn, vDataOut(), vAns vDataIn = Range("a1:a12") For n = LBound(vDataIn) To UBound(vDataIn) - 1 If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1) If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then ReDim Preserve vDataOut(x) vDataOut(x) = vDataIn(n, 1) + k: x = x + 1 End If Next 'k End If Next 'n vAns = InputBox("Enter the cell address of where to start putting the results") With Range(vAns).Resize(UBound(vDataOut) + 1, 1) .EntireColumn.ClearContents: .NumberFormat = "0000000000" .Value = Application.Transpose(vDataOut) End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
On Wed, 27 Feb 2013 12:21:42 -0800 (PST), GWC wrote:
This an example of part of my list: 050000698 050000703 How do I get a list of the numbers that are NOT in that list? For example: 050000699 050000700 050000701 050000702 Note: My entire list contains numbers from 050000698 to 051003040 You can do this with a macro. The macro below assumes that your list starts in A1 and continues down consecutively (no blank rows) as far as required. It places the results in column B. Those parameters can be changed if necessary. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =================================== Option Explicit Sub MissingNums() Dim vSrc As Variant, Nums() As Long Dim vRes() As Variant Dim rDest As Range Dim lFirst As Long, lLast As Long Dim i As Long, j As Long Set rDest = Range("B1") 'assume values are in column A starting at A1 'If there is a header, or they start at a different number ' change A1 below accordingly 'Also assumes no blanks. If there might be blanks, need to change code below vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ReDim Nums(1 To UBound(vSrc, 1)) For i = 1 To UBound(vSrc, 1) Nums(i) = vSrc(i, 1) Next i With WorksheetFunction lFirst = .Min(Nums) lLast = .Max(Nums) ReDim vRes(1 To lLast - lFirst + 1 - UBound(Nums), 1 To 2) For i = lFirst To lLast If IsError(Application.Match(i, Nums, 0)) Then j = j + 1 vRes(j, 1) = i End If Next i End With Application.ScreenUpdating = False rDest = "Missing Numbers" With rDest.Resize(rowsize:=UBound(vRes, 1)).Offset(rowoffset:=1) .Cells = vRes .EntireColumn.AutoFit .NumberFormat = "000000000" End With Application.ScreenUpdating = True End Sub ==================================== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
On Wed, 27 Feb 2013 21:11:56 -0500, Ron Rosenfeld wrote:
You can do this with a macro. Minor change to clear the column before writing results ======================================= Option Explicit Sub MissingNums() Dim vSrc As Variant, Nums() As Long Dim vRes() As Variant Dim rDest As Range Dim lFirst As Long, lLast As Long Dim i As Long, j As Long Set rDest = Range("B1") 'assume values are in column A starting at A1 'If there is a header, or they start at a different number ' change A1 below accordingly 'Also assumes no blanks. If there might be blanks, need to change code below vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ReDim Nums(1 To UBound(vSrc, 1)) For i = 1 To UBound(vSrc, 1) Nums(i) = vSrc(i, 1) Next i With WorksheetFunction lFirst = .Min(Nums) lLast = .Max(Nums) ReDim vRes(1 To lLast - lFirst + 1 - UBound(Nums), 1 To 2) For i = lFirst To lLast If IsError(Application.Match(i, Nums, 0)) Then j = j + 1 vRes(j, 1) = i End If Next i End With Application.ScreenUpdating = False rDest.EntireColumn.Clear rDest = "Missing Numbers" With rDest.Resize(rowsize:=UBound(vRes, 1)).Offset(rowoffset:=1) .Cells = vRes .EntireColumn.AutoFit .NumberFormat = "000000000" End With Application.ScreenUpdating = True End Sub ============================================== |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which numbers are missing?
Ron adds a good idea to autofit after dumping the results...
Sub GetMissingNumbers() Dim n&, k&, x&, vDataIn, vDataOut(), vAns vDataIn = Range("a1:a12") For n = LBound(vDataIn) To UBound(vDataIn) - 1 If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1) If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then ReDim Preserve vDataOut(x) vDataOut(x) = vDataIn(n, 1) + k: x = x + 1 End If Next 'k End If Next 'n vAns = InputBox("Enter the cell address of where to start putting the results") With Range(vAns).Resize(UBound(vDataOut) + 1, 1) .EntireColumn.ClearContents: .NumberFormat = "0000000000" .Value = Application.Transpose(vDataOut) .Columns(1).AutoFit End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Numbers Missing | Excel Worksheet Functions | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
Missing row numbers, HELP !!!! | Excel Discussion (Misc queries) | |||
row numbers missing etc | Excel Programming | |||
Missing Numbers | Excel Programming |