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.
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.
Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.
Pingback: Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager | Excel For You
Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You
Pingback: #Excel Super Links #15 – shared by David Hager | Excel For You
Pingback: #Excel Super Links #41 – shared by David Hager | Excel For You
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
Pingback: #Excel Super Links #100 (Special Edition) – shared by David Hager | Excel For You
Pingback: #Excel Super Links #150 – Special Edition | Excel For You
Pingback: New Excel #3 – Creating a Sorted Unique List from a Filtered Range Using Dynamic Arrays | Excel For You