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:

	A​	B​	C​
1​	Qty​	Helper​	​
2​	2​	0​	B2: =SUM(A1, B1)
3​	8​	2​
4​	6​	10​
5​	4​	16​
6​	2​	20​
7​	0​	22​
8​
9​	#​	Label​
10​	1​	1/2​	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)
11​	2​	2/2​
12​	3​	1/8​
13​	4​	2/8​
14​	5​	3/8​
15​	6​	4/8​
16​	7​	5/8​
17​	8​	6/8​
18​	9​	7/8​
19​	10​	8/8​
20​	11​	1/6​
21​	12​	2/6​
22​	13​	3/6​
23​	14​	4/6​
24​	15​	5/6​
25​	16​	6/6​
26​	17​	1/4​
27​	18​	2/4​
28​	19​	3/4​
29​	20​	4/4​
30​	21​	1/2​
31​	22​	2/2​
32​	23​	#DIV/0!​
33​	24​	#DIV/0!​

 

[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..!!