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
What I need to do is solve for multiple values at once, and using this technique for multiple columns doesn't do it.