Any way take numbers in a column and put them into a range?

[mcmuney]

Here's an example of what I need:

ColumnA
1
2
3
4
6
7
8
10
15
16

Instead of manually hunting down the gaps, is there any way for me to get the above shown in a range, like this?

1 - 4
6 - 8
10
15 - 16

OR

1 - 4, 6 - 8, 10, 15 - 16

OR

1 - 4; 6 - 8; 10; 15 - 16

Thanks in advance!

 

[MartyS]

Here's an example of what I need:

ColumnA
1
2
3
4
6
7
8
10
15
16

Instead of manually hunting down the gaps, is there any way for me to get the above shown in a range, like this?

1 - 4
6 - 8
10
15 - 16

OR

1 - 4, 6 - 8, 10, 15 - 16

OR

1 - 4; 6 - 8; 10; 15 - 16

Thanks in advance!

Hi,

See if this helps!



You'll need to edit the CONSTS at the beginning of the macro to make sure it matches your requirements.


E.G. if your source data is in column A
with the header in A1
so the actual data starts in A2
and the sheetname is "MyData"

and..

you want the output data to start in cell D1 (the data goes down rows), populating the next cell down, and so on, for each "band" found.

then:



Const StartDataRow = 2 '2
Const StartDataCol = 1 'A

Const EndDataRow = 1048576 'Highest Row in Excel. Do not alter.

Const SheetName = "MyData" 'MyData worksheet

Const StartDataOutputRow = 1 '1
Const StartDataOutputCol = 4 'D

Sub PutBins()


' Splits out run of numbers based on gaps found


' NOTE: Assumes the numbers are sorted ascending.


' IMPORTANT NOTE: Ensure that ONLY the desired data is in the data column. If there is other data BELOW the end of the actual data, it gets included because
' the program ascetains the LAST ROW used in the specified column, denoted by the STARTDATACOL Const




    Const StartDataRow = 2          'The row where the source data starts (NOT including the header). If header in Row 1, then data will start in row 2.
    Const StartDataCol = 1          'The column containing the data. For column A, put 1, for B, 2 and so on (A=1, B=2, C=3, D=4.. .. ..)
    Const EndDataRow = 1048576      'Highest Row in Excel. Do not alter.
    Const SheetName = "Sheet3"      'Sheetname to work in. Ensure you've got the correct sheetname BEFORE running!
    
    Const StartDataOutputRow = 1    'The ROW where you want the BIN data to go (the grouped information)
    Const StartDataOutputCol = 13   'The COLUMN where you want the BIN data to go. A=1, B=2, C=3 and so on.
    
    Dim LastRow As Integer
    
    Dim rng As Range
    Dim Ptr As Integer
    Dim LstPtr As Integer
    Dim Mkr As Integer
    Dim Cntr As Integer
    Dim MArray() As Integer
    Dim ArrayCnt As Integer
    
    'Get the Last Row in the worksheet
    LastRow = Sheets(SheetName).Range(Cells(StartDataRow, StartDataCol).Address, Cells(EndDataRow, StartDataCol).Address).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Cntr = 0: LstPtr = 0: ArrayCnt = 0
    For Each rng In Sheets(SheetName).Range(Cells(StartDataRow, StartDataCol).Address, Cells(LastRow, StartDataCol).Address)
        Cntr = Cntr + 1
        If Cntr = 1 Then Mkr = rng.Value
        Ptr = rng.Value
        If LstPtr <> Ptr - 1 And Ptr <> 0 Then
            ArrayCnt = ArrayCnt + 1
            ReDim Preserve MArray(1 To 2, 1 To ArrayCnt)
            MArray(1, ArrayCnt) = Mkr
            MArray(2, ArrayCnt) = LstPtr
            Mkr = Ptr
        End If
        
        LstPtr = Ptr
        
    Next rng
    
    If Ptr <> 0 Then
    
        ArrayCnt = ArrayCnt + 1
        ReDim Preserve MArray(1 To 2, 1 To ArrayCnt)
        MArray(1, ArrayCnt) = Mkr
        MArray(2, ArrayCnt) = LstPtr
        Mkr = Ptr
    
    End If
    
    For Cntr = 1 To ArrayCnt
        Sheets(SheetName).Range(Cells(StartDataOutputRow + Cntr - 1, StartDataOutputCol).Address).Value = "'" & MArray(1, Cntr) & " - " & MArray(2, Cntr)
    Next Cntr
    
End Sub

 

[Eric W]

If you have a version Excel 2016 or later with the CONCAT function (not CONCATENATE), then this might work:

	A	B	C
1	Header
2	1		1-4, 6-8, 10, 15-16,
3	2
4	3
5	4
6	6
7	7
8	8
9	10
10	15
11	16
12

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula C2 {=CONCAT(CHOOSE((IFERROR(A2:A11-A1:A10,0)=1)*2+(A3:A12-A2:A11=1)+1,A2:A11&", ",A2:A11&"-",A2:A11&", ",""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>