Matthew29464
New Member
I need 2 formulas. In both, I am trying to create a list from a table. I've attached an example with both questions on the example. Thanks for any help.
Creating a list
- Thread starter Matthew29464
- Start date
Peter Bartholomew
Well-Known Member
The second part of you query has a straightforward formula provided you are using Office 2013 (I think) or later
= TEXTJOIN( ",", 1, NamesWithBlanks )
where the parameters are "," to specify the separator, 1 or TRUE to omit blanks and the name of your range or array.
The first part of the query is reasonable direct using Office 365 but a mess on older versions.
= COUNTIFS( TableNames, TableNames )
will return the number of times each name occurs in the list ( I have used a Named formula 'occurrences' for this to simplify subsequent formulas ).
The next step is to sort the list in descending order of occurrences
= SORTBY( TableNames, occurrences,-1 ) )
The next step is to filter the sorted list (as a nested formula)
= UNIQUE( SORTBY( TableNames, occurrences,-1 ) )
In this case I defined the formula to be 'distinctNames', giving the final formula
= TEXTJOIN( ", ", 1, distinctNames )
If you do not like or understand named formulas, each name may be substituted by its formula, giving a single multiply-nested result.
If you do not have access to dynamic arrays, it is still possible with legacy Excel but Power Query would be far more straightforward.
= TEXTJOIN( ",", 1, NamesWithBlanks )
where the parameters are "," to specify the separator, 1 or TRUE to omit blanks and the name of your range or array.
The first part of the query is reasonable direct using Office 365 but a mess on older versions.
= COUNTIFS( TableNames, TableNames )
will return the number of times each name occurs in the list ( I have used a Named formula 'occurrences' for this to simplify subsequent formulas ).
The next step is to sort the list in descending order of occurrences
= SORTBY( TableNames, occurrences,-1 ) )
The next step is to filter the sorted list (as a nested formula)
= UNIQUE( SORTBY( TableNames, occurrences,-1 ) )
In this case I defined the formula to be 'distinctNames', giving the final formula
= TEXTJOIN( ", ", 1, distinctNames )
If you do not like or understand named formulas, each name may be substituted by its formula, giving a single multiply-nested result.
If you do not have access to dynamic arrays, it is still possible with legacy Excel but Power Query would be far more straightforward.
GraH - Guido
Well-Known Member
@Peter Bartholomew, textjoin is only available in 365 subscription. It is not even around in 2016 desktop version :-(
Peter Bartholomew
Well-Known Member
Guido Thank you for the correction; I had used Office 2010 before installing 365 semi-annual so I couldn't pinpoint the change from personal experience. I remember CONCATENATE as just about the most annoyingly pointless functions ever, given that I programmed Excel using array formulas just about all the time; why should the function even exist if it is not capable of concatenating the elements of an array?
One example was a spreadsheet I wrote to translate numbers (digits) into German text. The formula was about 6 levels deep in named formulas to avoid the use of helper cells and the dreaded implicit intersection. Having derived an array containing text for the units, thousands, millions and billions all I needed to do was concatenate them to get the result. I didn't even need TEXTJOIN, CONCAT would have done at a pinch
= CONCAT( IF( (k>1)*(LEN(groups)>1), ", ", "" ) & groups )
I was so happy to consign CONCATENATE to the trash can of history where it belongs!
One example was a spreadsheet I wrote to translate numbers (digits) into German text. The formula was about 6 levels deep in named formulas to avoid the use of helper cells and the dreaded implicit intersection. Having derived an array containing text for the units, thousands, millions and billions all I needed to do was concatenate them to get the result. I didn't even need TEXTJOIN, CONCAT would have done at a pinch
= CONCAT( IF( (k>1)*(LEN(groups)>1), ", ", "" ) & groups )
I was so happy to consign CONCATENATE to the trash can of history where it belongs!