Parsing Numeric Ranges....

[dan_l]

Hi,

I'm struggling mightily to wrap my head around this one. I've got ideas, but every time I sit down to do it I end up going down a rabbit hole. I've got a bunch of numbers like:

1
2
3
9
14
15
16
19

I want to parse them as a FROM and a TO like:
From To
1 3
9 9
14 16
19 19


Note how the 2 would be included in 1-3 and 15 would be included in 14-16. Data will not necessarily be sorted natively.


I'm looking for a pure code solution. I don't even need somebody to write it out all the way or anything I'm just looking for broader advice on how it would be coded out.

 

[Kenneth Hobson]

Input in column A, would output be in column B and C, or just B with space character separator, or in an array?

 

[NARAYANK991]

Hi ,

You say :

Data will not necessarily be sorted natively.

But does this mean that the code itself cannot sort the data as a first step ?

Narayan

 

[dan_l]

Kenneth: Output will be 2 different cells. The output is going to get fed into our ERP environment.

Naray: the code can do the sort if need be. But ‘as it is found’ it will be unsorted.

 

[Debaser]

Broadly speaking:

Sort the data
Create storage for the output (array, range, collection whatever)
Set first band start and finish in the storage to the first item
Loop from second item, checking if it is previous item + 1.
If so, change finish item to current item and move to next item.
If not, move to next storage row, and set start and finish to current item.
Repeat to the end.

 

[Kenneth Hobson]

Could there be any duplicate numbers in the unsorted range?

 

[Kenneth Hobson]

I will work on the do loop more later. For now, this is my approach. I may or may not use PosInArray.

Sub Main()
  Dim r As Range, a, aa, m, i As Long, j As Long, p1, p2, p3
  Dim tf As Boolean
 
  'Range to with integers to sequence.
  Set r = Range("A2", Range("A2").End(xlDown))
  a = ArrayListSort(r.Value) 'Sort array of integers.
  m = UBound(a) 'last element location in sorted array.
 
  ReDim aa(1 To m + 1, 1 To 2) 'output array to hold sequence parts
  aa(1, 1) = a(0) 'Assign first sequence part
  i = 0 'element 1 in a
  j = 1 'element 1 in aa
 
  Do
    p1 = PosInArray(aa(j, 1), a)
    Do
      If aa(j, 2) = "" Then '2nd part of integer sequence not filled.
        p2 = p1 + 1 'increment sequence
        If PosInArray(p2, a) > p1 Then
         
        End If
      End If
    Loop Until a(j, 2) <> ""
    j = j + 1 'increment sequence array to next "row"
  Loop Until tf
 

End Sub

'If array is 0 based, 1 is returned if aValue matches anArray(0). -1 if not found
Function PosInArray(aValue, anArray)
  Dim pos As Long
  On Error Resume Next
  pos = -1
  pos = WorksheetFunction.Match(CStr(aValue), anArray, 0)
  PosInArray = pos
End Function

Function ArrayListSort(a As Variant, Optional bAscending As Boolean = True)
  Dim cl
  With CreateObject("System.Collections.ArrayList")
    For Each cl In a
      .Add cl
    Next
   
    .Sort 'Sort ascendending
    If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
    ArrayListSort = .Toarray()
  End With
End Function