Need to find sequential serial numbers and group them counting # of #

[csharpe3]

I have serial numbers on the same product that is numbered sequentially. For some reason we have sold individual serial numbers and they are no longer consecutive. I need to be able to find sequential numbers, Label them as consecutive and display how many consecutive numbers are in a group and what number that unit is of the group.

The goal is to be able to find a customer the smallest block of consecutive numbers that meet their needs without breaking larger groups of consecutive numbers.

Columns = Description = Current formula
Number = Serial numbers sorted smallest to largest = No formula
Group = Is the serial part of a sequential group or not sequential. =

=IF(A1=A2-1,"Consecutive","Not")

1 = What is the sequence of the number in the group =

=IF(B2="Consecutive",1+C1,1)

Of Group = "This is what I need help with. I want to display the number of this serial number in the total of the consecutive numbers. =

Need help

Sample

Number	Group	1	Single	of Group
2105590001	Not	1	Group	1 of 5
2105590002	Consecutive	2	Group	2 of 5
2105590003	Consecutive	3	Group	3 of 5
2105590004	Consecutive	4	Group	4 of 5
2105590005	Consecutive	5	Group	5 of 5
2105590007	Not	1	Group	1 of 3
2105590008	Consecutive	2	Group	2 of 3
2105590009	Consecutive	3	Group	3 of 3
2105590011	Not	1	single	1 of 1
2105590013	Not	1	Group	1 of 3
2105590014	Consecutive	2	Group	2 of 3
2105590015	Consecutive	3	Group	3 of 3
2105590017	Not	1	Group	1 of 2
2105590018	Consecutive	2	Group	2 of 2
2105590020	Not	1	single	1 of 1
2105590022	Not	1	single	1 of 1
2105590024	Not	1	single	1 of 1
2105590026	Not	1	Group	1 of 2
2105590027	Consecutive	2	Group	2 of 2
2105590029	Not	1	single	1 of 1
2105590031	Not	1	single	1 of 1
2105590033	Not	1	Group	1 of 18
2105590034	Consecutive	2	Group	2 of 18
2105590035	Consecutive	3	Group	3 of 18
2105590036	Consecutive	4	Group	4 of 18
2105590037	Consecutive	5	Group	5 of 18

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[MickG]

If you're happy to do it with code , try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Oct19
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] r       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
    
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(1) = Dn + 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] r = 1 To nRng.Count
                nRng(r).Offset(, 4) = r & " of " & nRng.Count
            [COLOR="Navy"]Next[/COLOR] r
         [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Set[/COLOR] nRng = Nothing
    [COLOR="Navy"]End[/COLOR] If


[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[csharpe3]

Re: Need to find sequential serial numbers and group them counting # of # - solution

Mick,

Thank you.

Others, If you need to use this code, Select the column you want the data to appear and run the macro. Works really nice.

If you're happy to do it with code , try this:-

[COLOR=Navy]Sub[/COLOR] MG28Oct19
[COLOR=Navy]Dim[/COLOR] Rng     [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn      [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] nRng    [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] r       [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]Set[/COLOR] nRng = Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR=Navy]End[/COLOR] If
    
    [COLOR=Navy]If[/COLOR] Not Dn.Offset(1) = Dn + 1 [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]For[/COLOR] r = 1 To nRng.Count
                nRng(r).Offset(, 4) = r & " of " & nRng.Count
            [COLOR=Navy]Next[/COLOR] r
         [COLOR=Navy]End[/COLOR] If
            [COLOR=Navy]Set[/COLOR] nRng = Nothing
    [COLOR=Navy]End[/COLOR] If


[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Re: Need to find sequential serial numbers and group them counting # of # - solution

You're welcome
Regrds Mick