Number Ranges/Series

[duffy01]

Hi All,

I'm trying to get a set of data into a certain format, automatically:

In Column A I have the following type of data -

3
4
5
6
9
11
14
15
16
17
33
37

1) I want to return the consecutive number series with less than 3 numbers like: 1,2

2) I also want to return the consecutive number series with 3 or more numbers like: 14-17

3) Then, I want the output of the data to be separated by commas.

4) That result would then be put in parentheses.

5) A count of the numbers would then appear before the parentheses.

The end result of the above example data would look like:

12 (3-6,9,11,14-17,33,37)

Can anyone help?

Thanks in advance!

Duff

 

[MickG]

Hi, This could possibly be a lot simpler , but it seems to work.

[COLOR="Navy"]Sub[/COLOR] MG28Jan37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
c = c + 1
[COLOR="Navy"]If[/COLOR] c = 1 And Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR] txt = txt & Dn
[COLOR="Navy"]If[/COLOR] c = 1 And Not Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR] txt = txt & Dn & ","
[COLOR="Navy"]If[/COLOR] Not c = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(1) = Dn + 1 And Dn.Offset(-1) + 1 = Dn [COLOR="Navy"]Then[/COLOR]
        txt = txt & "-" & Dn & ","
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]If[/COLOR] Dn.Offset(1) = Dn + 1 And Not Dn.Offset(-1) + 1 = Dn [COLOR="Navy"]Then[/COLOR]
        txt = txt & Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Not Dn.Offset(-1) + 1 = Dn And Not Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR]
        txt = txt & Dn & ","
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox Rng.Count & " (" & txt & ")"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[duffy01]

Mick,

This works fantastic for the example I gave.

How do I get it to work on a bigger range of numbers?
(i.e. cell A1 to A400)?

Thx

Duff

 

[MickG]

Hi, The code should work for as many rows as you have filled with numbers in column "A", But you will end up with a rather large Msgbox.
What would you like to do with the result, put them in a range somewhere ???
Regards Mick

 

[duffy01]

Yes.. I need to return that msgbox data into a cell (the last number can't have a comma after it though)

Thanks... you rule!!

Duff

*Preferably cell B1.. thx!!

 

[MickG]

Hi, Remove the magbox bit & replace with the code below.

txt = Left(txt, Len(txt) - 1)
[COLOR="Navy"]With[/COLOR] Range("b1")
    .Value = Rng.Count & " (" & txt & ")"
    .WrapText = True '[COLOR="Green"][B] remove this if not wanted[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

Regards Mick

 

[duffy01]

Mick,

This works 99.8% (which is a lot more than I had!)

If I have two consecutive numbers only, it puts a dash there.

I need anything less than 2 to be commas, and anything 3 or more to be the dash.

Thanks for all your help so far... this is incredible!!

Duff

 

[MickG]

Hi, Duff, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Jan17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] com
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
c = c + 1
[COLOR="Navy"]If[/COLOR] c = 1 And Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR] txt = txt & Dn
[COLOR="Navy"]If[/COLOR] c = 1 And Not Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR] txt = txt & Dn & ","
[COLOR="Navy"]If[/COLOR] Not c = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(1) = Dn + 1 And Dn.Offset(-1) + 1 = Dn [COLOR="Navy"]Then[/COLOR]
        txt = txt & "-" & Dn & ","
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]If[/COLOR] Dn.Offset(1) = Dn + 1 And Not Dn.Offset(-1) + 1 = Dn [COLOR="Navy"]Then[/COLOR]
        txt = txt & Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Not Dn.Offset(-1) + 1 = Dn And Not Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR]
        txt = txt & Dn & ","
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
txt = Left(txt, Len(txt) - 1)
    com = Split(txt, ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(com)
    [COLOR="Navy"]If[/COLOR] InStr(com(n), "-") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Val(Split(com(n), "-")(0) + 1) = Val(Split(com(n), "-")(1)) [COLOR="Navy"]Then[/COLOR]
            com(n) = Replace(com(n), "-", ",")
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Range("b1")
    .Value = Rng.Count & " (" & Join(com, ",") & ")"
    .WrapText = True '[COLOR="Green"][B] remove this if not wanted[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[duffy01]

Mick,

You are the man!! This works flawlessly!

Duff