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:
The following defined names are needed to construct the formula.
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):
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.