I'm working on a D&D character sheet model in Excel (2021), which uses a Table to fill in the data per level. The current thing I'm running into is the Class and Level field, which tells you how many levels you have taken in each class, and what subclass each has (if any). It's a bit long to explain in text so here is a simplified version of the end result I am looking for:

Level	Class	Subclass	Tot_Class
1	Wizard		Wizard 1
2	Cleric	Arcana	Wizard 1, Arcana Cleric 1
3	Cleric	Arcana	Wizard 1, Arcana Cleric 2
4	Wizard	Illusion	Illusion Wizard 2, Arcana Cleric 2

I'm working on a D&D character sheet model in Excel (2021), which uses a Table to fill in the data per level. The current thing I'm running into is the Class and Level field, which tells you how many levels you have taken in each class, and what subclass each has (if any). It's a bit long to explain in text so here is a simplified version of the end result I am looking for:

Level	Class	Subclass	Tot_Class
1	Wizard		Wizard 1
2	Cleric	Arcana	Wizard 1, Arcana Cleric 1
3	Cleric	Arcana	Wizard 1, Arcana Cleric 2
4	Wizard	Illusion	Illusion Wizard 2, Arcana Cleric 2

Of course I could input these things manually, but I would prefer a formula to calculate the Tot_Class per row.

The formula I've gotten so far is: =TEXTJOIN(", ";TRUE;(UNIQUE(FILTER([Class];([Level]<=[@Level])*([Level]>0))))) This will result in "Wizard, Cleric" at Level 4, which obviously is only part of the equation. I'm thinking that functions such as the LET function will probably be helpful here, but I don't want to spend hours on something that just isn't going to work, so I would love some insight on how to set up this formula if possible.

TLDR: I need a formula that counts each unique value in the Class column up until that row's level, then create a string which gives (for each class): the last given subclass for that class + Class value + Count of class in column up to current level + ", " as delimiter.

• excel
• excel-formula
• conditional-statements
• cell
• excel-2021

Share Improve this question Follow edited Feb 22 at 15:46 Black cat 6,29755 gold badges2929 silver badges6161 bronze badges asked Nov 20, 2024 at 11:21 Lyriander NBLyriander NB 1311 silver badge33 bronze badges 2

• For level 4 you show Illusion Wizard 2, Arcana Cleric 2 while literally the data shows Wizard 1, Illusion Wizard 1, Arcana Cleric 2 should subclass be ignored in the count for class? I think adding a little more examples would help. – P.b Commented Nov 20, 2024 at 12:26
• Context: In this system, classes gain access to a subclass at different levels. They can only ever get a single subclass, which stays the same for every level afterwards. While Cleric gains a subclass at their first level, Wizards only gain a subclass at their second level. So when another level is put into wizard at level 4, it gains its subclass, meaning it needs to be added to the Tot_Class. You can choose different Classes to level up every time, all with their own subclass, the formula needs to keep that in mind. So, yes, you only count the Class, but add subclass to name if it has one. – Lyriander NB Commented Nov 20, 2024 at 15:39

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

The formula does:

• Define unique Classes
• Count the occurence of each
• Detect the last subclass for each
• Create concatenated text

The formula in cell I2

=LET(cl,UNIQUE($B$2:B2),
cntcl,COUNTIF($B$2:B2,cl),
last,XLOOKUP(cl,$B$2:B2,$C$2:C2,,0,-1),
TEXTJOIN(", ",TRUE,last&" "&cl&" "&cntcl))

In cell F2 if upgrade to 365 or TAKE is avail.

=LET(cl,UNIQUE(TAKE(Table1[Class],ROW()-1)),
cntcl,COUNTIF(TAKE(Table1[Class],ROW()-1),cl),
last,XLOOKUP(cl,TAKE(Table1[Class],ROW()-1),TAKE(Table1[Subclass],ROW()-1),,0,-1),
TEXTJOIN(", ",TRUE,last&" "&cl&" "&cntcl))

EDIT
On comment of the asker:
Removed unnecessary space from the end of the strings.

Okay, I have confirmed my main worry about this solution (i.e. under current rules, no, you cannot have multiple subclasses for the same class), so I think it should work. (Formula written for Level 3)

=LET(_class, B$2:B4, _subclass, C$2:C4,
    c_Unq, UNIQUE(_class),
    s_Unq, UNIQUE(FILTER(HSTACK(_class,_subclass), _subclass<>"", {"",""}), TRUE),
    _classLevels, MAP(c_Unq, LAMBDA(_c, TEXTJOIN(" ", TRUE, IFERROR(VLOOKUP(_c, s_Unq, 2, FALSE),""), _c) & " " & COUNTIF(_class, _c))),
    TEXTJOIN(", ",TRUE, _classLevels)
)

This will create a Unique list of Classes (c_Unq), and a unique table of Class/Subclass pairs (s_Unq; ignores classes without subclasses).

_class	_subclass
Wizard
Cleric	Arcana
Cleric	Arcana

c_Unq
Wizard
Cleric

s_Unq[Class]	s_Unq[Subclass]
Cleric	Arcana

It will then run through the list of Unique Classes, prefix them with the Subclass if it exists, and append the count of how many times that Class appears to the end.

_classLevels
Wizard 1
Arcana Cleric 2

Finally, it will use TEXTJOIN to combine that list into a single value

Wizard 1, Arcana Cleric 2

I think this would work in Excel 2021:

=LET(c,INDEX([Class],1):[@Class],s,INDEX([Subclass],1):[@Subclass],u,UNIQUE(c),TEXTJOIN(", ",,TRIM(u&" "&XLOOKUP(u,c,s,,,-1)&" "&COUNTIF(c,u))))