Christopher Schene

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

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.

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.