• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a list

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.
 

Attachments

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.
 

Attachments

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!
 
Top