Link to home
Start Free TrialLog in
Avatar of Christopher Schene
Christopher ScheneFlag for United States of America

asked on

How do I sort an excel spread of random Hash values in one and I want to most frequently matcing set first

here is my table
name, hash, phone
chris, random hasha, phone chris
george, random hashn, phone george
kyle, random hasha, phone kyle
sally, random hashn, phone sally
kathy, random hasha, phone kathy

After the table is sorted I want it sorted by the has id's with most frequently occurring at the top and by decending count
chris, random hasha, phone chris
kyle, random hasha, phone kyle
kathy, random hasha, phone kathy
george, random hashn, phone george
sally, random hashn, phone sally

Excel only gives me the option to sort numerically or alphanumerically, I want to sort by match and I want the items that match the most in descending order at the top of the list
Avatar of Shums Faruk
Shums Faruk
Flag of India image


Is this with Commas in between? and the data is in one Column?
Sample workbook would be helpful with expected data.
Avatar of Gary Kline
Gary Kline
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Manual approach would be:
1. Split single column in 3 columns through Text To Columns Comma as delimiters
2. Sort by column B
3. In column D below formula to put it back with comma:
=A2&", "&B2&", "&C2
Final Result would be as below image:
User generated imageThis is what you want?
Avatar of Christopher Schene


Give a day to test both approaches
I successfully used ID: 42080754

That is really clever: You must be an excel guru!


Please explain  how it works. Thanks.
Really clever solution! Impressive.
For Christopher:

When doing complicated sorts like yours, one solution is to fill an adjacent column with a custom index value. By calculating a text value with different parts (the most important part is first, the least important at the end), we can get fairly precise in our sorting.

For example, your sort order was to be:
1) Count of hash values, largest count first; and
2) Current hash value.

So the formula creates a custom index value with 1) the total count of the current hash value ( COUNTIF(range,value) ), followed by 2) the current hash value.  

Note that the entire index value will be sorted either ascending or descending.   If you had wanted a second sort value (like the current hash value) to be sorted in ascending order, it should not be included in your custom index. In that case you would use two columns to sort by; first the custom index column (Descending order), then the hash value column (Ascending order).

The TEXT function was used to convert the hash value count to text, while 'left-filling' with zeroes.  If this wasn't done, Excel's alphabetic sort would not have sorted in the order we are expecting.

Hope that helps!