Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

You can do a three level sort to solve this

  1. By Name {z to a}
  2. By Description (click on Custom List, then enter in Always, Sometimes, Usually)
  3. then by Percent (smallest to largest)

Pls see screenshot (from xl2010 below) sorted

share|improve this answer
    
-1 this does not sort according to the requirements - I want to sort the always percent from smallest to largest. –  chris neilsen Sep 19 '11 at 3:11
1  
Chris, I accept I made a typo in point 3, albeit my graphic being correct. I will update accordingly. Why I am relatively new here I am a little surprised you chose to downrank rather than make the observation, but I'll take that on the chin. cheers. –  brettdj Sep 19 '11 at 3:23
    
the down vote is because your method does not sort according to the requirements layed out in the question. the requirement is to sort on Percent, but keep the names grouped together in Always, Somtimes, Usually order. The sample data set happens to sort C-B-A but that is only coinsidence. –  chris neilsen Sep 19 '11 at 3:30
    
That wasn't my original interpretation, but I understand your view and think you are on balance correct. Nupur? –  brettdj Sep 19 '11 at 3:44
1  
Hey guys! Thank you for your responses which made it finally worked. I truly appreciate your time. –  Nupur Sep 21 '11 at 0:58
show 1 more comment

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.

  1. Always
  2. Sometimes
  3. 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

  1. 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
  2. Convert the cells in this new column from formulae to values, so the values are not changed during the sort
  3. Sort the data on this new column (descending), with Name as the second sort key.

Detailed steps

In case the above is not enough info:

  1. Create a 4th column: let's call it Ordering
    • D1: Ordering
  2. Give the first three rows in Ordering the following formulae:
    • D2: =C2+2 (i.e. 54 in your example)
    • D3: =C2+1 (i.e. 53)
    • D4: =C2 (i.e. 52)
  3. Select those 3 cells, and Fill Down to the bottom of your data, so for example, the next 3 rows would contain:

    • D5: =C5+2 (i.e. 62 in your example)
    • D6: =C5+1 (i.e. 61)
    • D7: =C5 (i.e. 60)

    Note 1: I'm really impressed/amazed that Excel's "Fill Down" mechanism populates the formulae as you need it here, at least on Excel 2010 on Windows. Do check the resultant values yourself, to ensure the Mac one behaves the same.

    Note 2: Don't be temped to do the Sort at this point, as the relative references get broken during the sort, and you end with an #REF! in D2

  4. Copy the Ordering column to the clipboard
  5. Paste the Ordering column as Values, i.e. to replace the above formulae by the values that they calculated
  6. Now do your Custom sort
    1. On Ordering, Descending
    2. On Name, Ascending

With your original data, this gives the desired result.

The Sort on Name is for the case where 2 sets of 3 rows have the same Percent value. Otherwise the results for the two different Names could get mixed up (if Excel's sort doesn't retain the initial relative order)

share|improve this answer
add comment

select the collumns and in the context menu find the sorting menu and use custom sorting

if you want them grouped create another column with the always percent of the group (through data insertion or formula) and primary sort on that

share|improve this answer
    
You might want to be more specific, I'm not sure you can do what he wants with Data Sorting. –  Lance Roberts Sep 18 '11 at 21:48
    
I have tried that but custom sorting is not helping me with this thing. I did sort by name, then by description, then by percent but didn't work. Could you suggest any other way of doing this. Thanks! –  Nupur Sep 18 '11 at 21:56
    
@Nupur I think the best bet is a new collumn check the edit –  ratchet freak Sep 18 '11 at 22:13
    
@ Rachet Thanks but I could not understand what you are trying to suggest. Sorry! Can u explain again? –  Nupur Sep 18 '11 at 23:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.