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

C

csharpe3

New Member
Joined
Oct 28, 2013
Messages
2
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. =
Code: 
=IF(A1=A2-1,"Consecutive","Not")
1 = What is the sequence of the number in the group =
Code: 
=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. =
Code: 
Need help

Sample
[TABLE="class: grid, width: 352, align: center"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Number
[/TD]
[TD]Group[/TD]
[TD="align: right"]1[/TD]
[TD]Single[/TD]
[TD]of Group[/TD]
[/TR]
[TR]
[TD="align: right"]2105590001[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 5
[/TD]
[/TR]
[TR]
[TD="align: right"]2105590002[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 5[/TD]
[/TR]
[TR]
[TD="align: right"]2105590003[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]3[/TD]
[TD]Group[/TD]
[TD]3 of 5[/TD]
[/TR]
[TR]
[TD="align: right"]2105590004[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]4[/TD]
[TD]Group[/TD]
[TD]4 of 5[/TD]
[/TR]
[TR]
[TD="align: right"]2105590005[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]5[/TD]
[TD]Group[/TD]
[TD]5 of 5[/TD]
[/TR]
[TR]
[TD="align: right"]2105590007[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590008[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590009[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]3[/TD]
[TD]Group[/TD]
[TD]3 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590011[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590013[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590014[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590015[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]3[/TD]
[TD]Group[/TD]
[TD]3 of 3[/TD]
[/TR]
[TR]
[TD="align: right"]2105590017[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 2[/TD]
[/TR]
[TR]
[TD="align: right"]2105590018[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 2[/TD]
[/TR]
[TR]
[TD="align: right"]2105590020[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590022[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590024[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590026[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 2[/TD]
[/TR]
[TR]
[TD="align: right"]2105590027[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 2[/TD]
[/TR]
[TR]
[TD="align: right"]2105590029[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590031[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]single[/TD]
[TD]1 of 1[/TD]
[/TR]
[TR]
[TD="align: right"]2105590033[/TD]
[TD]Not[/TD]
[TD="align: right"]1[/TD]
[TD]Group[/TD]
[TD]1 of 18
[/TD]
[/TR]
[TR]
[TD="align: right"]2105590034[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]2[/TD]
[TD]Group[/TD]
[TD]2 of 18[/TD]
[/TR]
[TR]
[TD="align: right"]2105590035[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]3[/TD]
[TD]Group[/TD]
[TD]3 of 18
[/TD]
[/TR]
[TR]
[TD="align: right"]2105590036[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]4[/TD]
[TD]Group[/TD]
[TD]4 of 18[/TD]
[/TR]
[TR]
[TD="align: right"]2105590037[/TD]
[TD]Consecutive[/TD]
[TD="align: right"]5[/TD]
[TD]Group[/TD]
[TD]5 of 18[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
If you're happy to do it with code , try this:-
Code: 
[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
 
Upvote 0
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.


MickG said:
If you're happy to do it with code , try this:-
Code: 
[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
Click to expand...
 
Upvote 0
Re: Need to find sequential serial numbers and group them counting # of # - solution

You're welcome
Regrds Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
1K
felixstraube
F
K
  • Question Question
Compress consecutive numbers
Replies
9
Views
430
Fuji
F
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
905
felixstraube
F
S
  • Solved
Formula to Sum a Group based on Blank Spaces between Groups
2
Replies
10
Views
326
secrestj
S
J
  • Solved
using vba to automatically sort a table
Replies
4
Views
152
jbrown021286
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top