Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Does Excel have a feature or set of features that can be used to accomplish the opposite of sorting? So, rather than sorting rows so that similar values are contiguous, I want similar values optimally spaced apart.

In other words, given a list of unique items that share traits (so, column A is a unique ID and columns B:H are various properties with a limited set of possible values), I want them ordered so that any given group of rows will be representative of the whole. If 20% of my rows have value "XYZ" in column B, then I would like any given set of 10 contiguous rows to contain 2 with that value. But this needs to be true for columns C:H as well.

I can do this with a single property by sorting according to that column and putting numbers in an additional column based on this formula: =IF(C2=C1,J1+(100/(1+COUNTIF(C:C,C2))),(100/(1+COUNTIF(C:C,C2)))) I paste as values and then sort by this number. This does a pretty good job of mixing according to column C.

What I need to do is solve for multiple values at once, and using this technique for multiple columns doesn't do it.

share|improve this question
Is this downvoted because Excel can't do this? Or because the question is too specific of a scenario? Or . . . ? – Dane Jun 27 '13 at 20:38
I don't know why someone downvoted you and didn't say something. But here is my guess: Your question is way too long. Strip your question down to the very essential parts. Maybe half as long as it now is. And be very clear what you want. (This is the way I use to format my questions) – nixda Jun 27 '13 at 21:04
I agree with nixda, question is far too verbose for what is actually being asked. I'd consider stripping it down to the bare minimum and removing superfluous and irrelevant details. Otherwise it becomes too localised. – James Jun 27 '13 at 21:41
Thanks nixda and James. I've rewritten the question in a way that should get me most of the way there. – Dane Jun 28 '13 at 13:32

You must log in to answer this question.

Browse other questions tagged .