Numbering a multi-heading Excel column

[tdungdinh]

I have an Excel sheet that needs a numbered heading (column H in the photo). How can I use only functions rather than VBA in this case?

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	H	I	J
1	Order	Country	Score
2	I	RUSSIA	38
3	a	Class 10	8
4	1	Puskin Ramsey	8
5	b	Class 11	7
6	1	Lenina	7
7	c	Class 12	23
8	1	Paroski Andrei	8
9	2	Lewandos Expki	8
10	3	Alexander Wang	7
11	II	VIETNAM	34
12	a	Class 10	10
13	1	Hoang Van An	10
14	b	Class 11	17
15	1	Nguyen Van Binh	9
16	2	Tran Tuan Anh	8
17	c	Class 12	7
18	1	Le Tuan Anh	7
Sheet8

Cell Formulas
Range	Formula
H2:H18	H2	=IF(ABS(CODE(RIGHT(I2))-77.5)<13,ROMAN(SUMPRODUCT(--(ABS(CODE(RIGHT(I$1:I1))-77.5)<13))+1),IF(LEFT(I2,5)="class",CHAR(COUNTIF(INDEX(I:I,AGGREGATE(14,6,ROW(I$2:I2)/(ABS(CODE(RIGHT(I$2:I2))-77.5)<13),1)):I2,"class*")+96),N(H1)+1))

Countries are identified by the last letter being capitalized, and the classes by the word class on the left.

 

[tdungdinh]

Countries are identified by the last letter being capitalized, and the classes by the word class on the left.

Really thanks so big for your useful answer. However, my further purpose is giving a multi-heading number for any 3 or 4-level pivot table (as my regular reports). So the above quotation is only applied for this case (with level 1 country, level 2 class). Is there any possible solution for every 3 or 4-level pivot table of which headings have no similar details?

p/s: I have no knowledge about VBA, so function is preferred.

 

[Eric W]

Sorry, I don't know of any way to create 3/4-level headings for a pivot table, especially when I don't have any specific way to identify what level each heading might be. There might be some way in the pivot table settings directly, but I don't know enough about them to say for sure.