• 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.

Sorting a CSE Array

N

Nothing Better Than Excel

New Member
Goal: sort an array (in an array-formula) within the formula either with standard functions or via a UDF.

Current Challenge: take a very large column of repeating values and identifying the five most-frequently occurring values (there will be many repeating values with the same frequency).

Approach: What I have currently tried is to come up with a unique list of values and then count the number of times they appear in the list. Now I have an array of entries with nn@item format. All I have to do here is sort the list and then toss content to the right of the fifth char(10).

{=StringConcat(CHAR(10),COUNTIF(DataQuality!AS2:AS200,"="&ArrayUnique(DataQuality!AS2:AS200&""))&"@"&ArrayUnique(DataQuality!AS2:AS200&""))}

STRINGCONCAT is a UDF that takes an array and creates a single text string with each array entry separated by the first parameter - in this case the new line, or char(10), character. I use this to put the values of an array into a single cell.

DataQuality!AS2:AS200 is a sample of my data for testing. Actual volume of data is about 35,000 entries with about 50 unique values.

ARRAYUNIQUE is a UDF that returns single values from an array with repetitive entries.

Thanks for all the brain power from those who will help me out here.

L

PS. is the array in an ARRAY FORMULA the same as the ARRAY in VBA?
 
Awesome Wally said:
Have a look here: http://chandoo.org/wp/2012/10/04/formula-forensics-no-030/

P.S. Can you share the StringConcat UDF? It may be very helpful for another forum question like this one: http://chandoo.org/forum/threads/cr...le-fields-based-on-variable-input-data.30305/
Click to expand...


Thank you for the link - though I read that prior to posting. The challenge with those approaches is that you need to use multiple columns and or multiple cells and requires multiple rows. It also requires manual intervention each time that the data change.

My challenge is that this is for an executive dashboard. Each CIO will be able to see their results by clicking a dropdown. For a variety of reasons (too long for posting here) a number of results each have to be contained within single cells. One reason I will mention is that by containing the results in a single cell the Excel CAMERA function (do you know about that?) can be more easily employed with more consistent results when the raw data are changed.


PS. I would SO like to take credit for StringConcat. I use it to display the results of almost all my array functions in single cells (don't forget to format TOP and WRAP for the cell). It also enables using multiple and varying content in dynamic email and OCS messages "mail merges." With all that in mind you can see the code I borrowed here: http://www.cpearson.com/excel/stringconcatenation.aspx
 
You must log in or register to reply here.
Back
Top