Macro help

[Alisya]

Hi, i need a macro that will produce result as shown in Col B, thus i want the code to create a reference for each set of Groups, i do have amounts in Col C.

Excel Workbook
	A	B
6	Group	Ref
7	NOMS	NO0001
8	NOMS	NO0002
9	NOMS	NO0003
10	NOMS	NO0004
11	NOMS	NO0005
12	NOMS	NO0006
13	NOMS	NO0007
14	LIFE	LI0001
15	LIFE	LI0002
16	LIFE	LI0003
17	LIFE	LI0004
18	LIFE	LI0005
19	LIFE	LI0006
20	LIFE	LI0007
21	LIFE	LI0008
22	LIFE	LI0009
23	JPMCASH	ETC
24	JPMCASH
25	JPMCASH
26	JPMCASH
27	JPMCASH
28	CITICASH
29	CITICASH
30	CITICASH
Sheet3

 

[VoG]

Try the formulas in C2 and B2 copied down

Excel Workbook
	A	B	C
1	Group	Ref	Nos
2	NOMS	NO0001	1
3	NOMS	NO0002	2
4	NOMS	NO0003	3
5	NOMS	NO0004	4
6	NOMS	NO0005	5
7	NOMS	NO0006	6
8	NOMS	NO0007	7
9	LIFE	LI0001	1
10	LIFE	LI0002	2
11	LIFE	LI0003	3
12	LIFE	LI0004	4
13	LIFE	LI0005	5
14	LIFE	LI0006	6
15	LIFE	LI0007	7
16	LIFE	LI0008	8
17	LIFE	LI0009	9
18	JPMCASH	JP0001	1
19	JPMCASH	JP0002	2
20	JPMCASH	JP0003	3
21	JPMCASH	JP0004	4
22	JPMCASH	JP0005	5
23	CITICASH	CI0001	1
24	CITICASH	CI0002	2
25	CITICASH	CI0003	3
Sheet63

 

[Alisya]

Vog, any chance doing this via VBA as I will be performing this task Daily and would like to eliminate manual intervention.

Try the formulas in C2 and B2 copied down

Excel Workbook
	A	B	C
1	Group	Ref	Nos
2	NOMS	NO0001	1
3	NOMS	NO0002	2
4	NOMS	NO0003	3
5	NOMS	NO0004	4
6	NOMS	NO0005	5
7	NOMS	NO0006	6
8	NOMS	NO0007	7
9	LIFE	LI0001	1
10	LIFE	LI0002	2
11	LIFE	LI0003	3
12	LIFE	LI0004	4
13	LIFE	LI0005	5
14	LIFE	LI0006	6
15	LIFE	LI0007	7
16	LIFE	LI0008	8
17	LIFE	LI0009	9
18	JPMCASH	JP0001	1
19	JPMCASH	JP0002	2
20	JPMCASH	JP0003	3
21	JPMCASH	JP0004	4
22	JPMCASH	JP0005	5
23	CITICASH	CI0001	1
24	CITICASH	CI0002	2
25	CITICASH	CI0003	3
Sheet63

 

[Alisya]

Is there a way for the formula to avoid putting numbers in Col C as i do have data from C:W

Try the formulas in C2 and B2 copied down

Excel Workbook
	A	B	C
1	Group	Ref	Nos
2	NOMS	NO0001	1
3	NOMS	NO0002	2
4	NOMS	NO0003	3
5	NOMS	NO0004	4
6	NOMS	NO0005	5
7	NOMS	NO0006	6
8	NOMS	NO0007	7
9	LIFE	LI0001	1
10	LIFE	LI0002	2
11	LIFE	LI0003	3
12	LIFE	LI0004	4
13	LIFE	LI0005	5
14	LIFE	LI0006	6
15	LIFE	LI0007	7
16	LIFE	LI0008	8
17	LIFE	LI0009	9
18	JPMCASH	JP0001	1
19	JPMCASH	JP0002	2
20	JPMCASH	JP0003	3
21	JPMCASH	JP0004	4
22	JPMCASH	JP0005	5
23	CITICASH	CI0001	1
24	CITICASH	CI0002	2
25	CITICASH	CI0003	3
Sheet63

 

[VoG]

Try

Sub Alisya()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .Formula = "=LEFT(A2,2)&TEXT(COUNTIF(A$2:A2,A2),""0000"")"
    .Value = .Value
End With
End Sub

 

[Alisya]

Try

Sub Alisya()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .Formula = "=LEFT(A2,2)&TEXT(COUNTIF(A$2:A2,A2),""0000"")"
    .Value = .Value
End With
End Sub

Vog on second thought how can we amend the code to just give 0001, 0002 etc up to the end of the data range.

 

[VoG]

Try

Sub Alisya()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .NumberFormat = "0000"
    .Formula = "=COUNTIF(A$2:A2,A2)"
    .Value = .Value
End With
End Sub