I have a data in excel in the format:
Description Name Percent
Always A 52
Sometimes A 23
Usually A 25
Always B 60
Sometimes B 30
Usually B 15
Always C 75
Sometimes C 11
Usually C 14
I want to sort this data:
For each name the sequence of description has to be same (eg: always followed by sometimes followed by usually) but for three names A, B and C, I want to sort the always percent from smallest to largest. Eg: I want the above example to look like this after sorting:
Description Name Percent
Always C 75
Sometimes C 11
Usually C 14
Always B 60
Sometimes B 30
Usually B 15
Always A 52
Sometimes A 23
Usually A 25
The always percent of name C was highest and always percent of name A was lowest. I hope I was able to explain it. I would really appreciate your help regarding the same.
3 Answers
You can do a three level sort to solve this
- By Name {z to a}
- By Description (click on Custom List, then enter in Always, Sometimes, Usually)
- then by Percent (smallest to largest)
Pls see screenshot (from xl2010 below)
I can't see a one-step approach, but try the following... (It's actually not as complicated as it looks: it's just hard to explain both succinctly and clearly!)
It is based on the assumption that the rows in the initial data are always in the desired relative order, i.e.
- Always
- Sometimes
- Usually
If this is true (rather than a coincidence in your example data), then you can create a 4th column, for the purposes of sorting, that generates numbers that can be sorted in your desired order.
Summary of approach
- Create a new column, containing data derived from your Percent values, that will retain the desired order of each set of 3 rows during sorting
- Convert the cells in this new column from formulae to values, so the values are not changed during the sort
- Sort the data on this new column (descending), with Name as the second sort key.
