Need help to generate serial numbers as explained in the picture

[amaresh achar]

Hi, I need help to generate serial numbers as explained in the image (below link) Sorry I didnt understand how to attach an image in this form.. Solution either in the form of formula or in the form of vba code, both are acceptable... Thank you in advance..

https://drive.google.com/file/d/1Mz_7t1TU4uomelgsB1Vp9wqNgrC42ajA/view?usp=sharing

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG22Aug32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 1 To Dn.Value
        c = c + 1
        [COLOR="Navy"]With[/COLOR] Cells(c, "F")
            .NumberFormat = "@"
            .Value = n & "/" & Dn.Value
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[shg]

Via an ugly formula:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

Helper​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td="bgcolor:#CCFFCC"]B2: =SUM(A1, B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

6​

[/td][td="bgcolor:#CCFFCC"]

10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

16​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

20​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

22​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#F3F3F3"]

#​

[/td][td="bgcolor:#F3F3F3"]

Label​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1​

[/td][td="bgcolor:#CCFFFF"]

1/2​

[/td][td="bgcolor:#CCFFFF"]B10: =MOD(A10 - 1 - LOOKUP(A10 - 1, $B$2:$B$7), LOOKUP(A10-1, $B$2:$B$7, $A$2:$A$7)) + 1 & "/" & LOOKUP(A10-1, $B$2:$B$7, $A$2:$A$7)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

2​

[/td][td="bgcolor:#CCFFFF"]

2/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

3​

[/td][td="bgcolor:#CCFFFF"]

1/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

4​

[/td][td="bgcolor:#CCFFFF"]

2/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

5​

[/td][td="bgcolor:#CCFFFF"]

3/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

6​

[/td][td="bgcolor:#CCFFFF"]

4/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

7​

[/td][td="bgcolor:#CCFFFF"]

5/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

8​

[/td][td="bgcolor:#CCFFFF"]

6/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

9​

[/td][td="bgcolor:#CCFFFF"]

7/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

10​

[/td][td="bgcolor:#CCFFFF"]

8/8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

11​

[/td][td="bgcolor:#CCFFFF"]

1/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

12​

[/td][td="bgcolor:#CCFFFF"]

2/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

13​

[/td][td="bgcolor:#CCFFFF"]

3/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

14​

[/td][td="bgcolor:#CCFFFF"]

4/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

15​

[/td][td="bgcolor:#CCFFFF"]

5/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

16​

[/td][td="bgcolor:#CCFFFF"]

6/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

17​

[/td][td="bgcolor:#CCFFFF"]

1/4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

18​

[/td][td="bgcolor:#CCFFFF"]

2/4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]

19​

[/td][td="bgcolor:#CCFFFF"]

3/4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

20​

[/td][td="bgcolor:#CCFFFF"]

4/4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

21​

[/td][td="bgcolor:#CCFFFF"]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]

22​

[/td][td="bgcolor:#CCFFFF"]

2/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]

23​

[/td][td="bgcolor:#CCFFFF"]

#DIV/0!​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]

24​

[/td][td="bgcolor:#CCFFFF"]

#DIV/0!​

[/td][td][/td][/tr]
[/table]

 

[amaresh achar]

Hi MickG ... Thank you very much for the code..!!! This is exactly what i wanted...!!

 

[amaresh achar]

Hi MickG ... Thank you very much for the code..!!! This is exactly what i wanted...!!

 

[MickG]

You're welcome

 

[amaresh achar]

Yeah.. great...Thanks..!!