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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
This is what you want?
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:
This is what you want?
ASKER
Give a day to test both approaches
ASKER
I successfully used ID: 42080754
That is really clever: You must be an excel guru!
Impressive.
Please explain how it works. Thanks.
That is really clever: You must be an excel guru!
Impressive.
Please explain how it works. Thanks.
ASKER
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!
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!
Is this with Commas in between? and the data is in one Column?
Sample workbook would be helpful with expected data.