Search code examples
excelaggregatesummary

How to create a 'summary' cell, that aggregates unique values from a column?

The closest I've been able to get is this text join:

Picture of attempt

 =TEXTJOIN(", ", TRUE, IF(MATCH(C5:C72, C5:C72, 0)=MATCH(ROW(C5:C72), ROW(C5:C72)), C5:C72, ""))

Is there any way to have the Cell at the top be limited to only one instance of C, R, E or D?

Thanks!

Solution

  • If one has the Dynamic Formula UNIQUE():

    =TEXTJOIN(",",,UNIQUE(MID(TEXTJOIN("",TRUE,C5:C72),SEQUENCE(LEN(TEXTJOIN("",TRUE,C5:C72))),1)))

    enter image description here

    If not:

    =TEXTJOIN(",",TRUE,IF(MATCH(MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),0)=ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),""))

    Depending on ones version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here

    To order it in the order you want use this formula

    Dynamic Array:

    =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH({"C","R","E","D"},TEXTJOIN("",,UNIQUE(MID(TEXTJOIN("",TRUE,C5:C72),SEQUENCE(LEN(TEXTJOIN("",TRUE,C5:C72))),1))))),{"C","R","E","D"},""))

    Older version:

    =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH({"C","R","E","D"},TEXTJOIN("",TRUE,IF(MATCH(MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),0)=ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),MID(TEXTJOIN("",TRUE,C5:C17),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(TEXTJOIN("",TRUE,C5:C17)))),1),"")))),{"C","R","E","D"},""))

    Again depending on one's version it will require the use of Ctrl-Shift-enter instead of Enter when exiting edit mode.