CODEWITHSUNDEEP
excelaggregatesummary
creddit
creddit

Reputation: 50

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!

Upvotes: 0

Views: 58

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

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.

Upvotes: 1

Related Questions