Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

The following defined names are needed to construct the formula.

Rge=Sheet1!$A$2:$A$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items.

tj_subtotal

In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t

You can download the file here.

textjoin_subtotal1

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

Advertisements

6 thoughts on “Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

  1. Pingback: Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager | Excel For You

  2. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  3. Pingback: #Excel Super Links #15 – shared by David Hager | Excel For You

  4. Pingback: #Excel Super Links #41 – shared by David Hager | Excel For You

  5. Pingback: Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager | Excel For You

  6. Pingback: #Excel Super Links #100 (Special Edition) – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s