excelstringexcel-formulaexcel-2021

EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?


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.


Solution

  • The formula does:

    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.

    enter image description here