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)) |
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?Countries are identified by the last letter being capitalized, and the classes by the word class on the left.