How to do this kind of sorting?
Referring to Excel General Question at 12/12/2006 5:09 PM PST
Does anyone know how to perform this kind of sorting in excel?
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
The above values are listed from cell A1 to A14
A1 has a higher priority than A2 on selection, and A14 has the lowest
priority on selection, because it located on the bottom of the list. I need
to select the top five numbers from the list without duplication, but the
distance between any two numbers must be bigger than / [equal to] the
smallest value from the list. On the other words, 2 is the smallest number
from the list,
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
the first number is 13 for selection, and
[13]
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
the second number is 8, which 13-8=5 and is bigger than / equal to the
smallest number 2.
[13 8]
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
the third number cannot be 7, because abs(8-7)=1, which is less than the
smallest number 2, even through abs(13-7)=6, which is bigger than 2.
the next third number cannot be 7 again, then skip it for the next one.
the next third number cannot be 14, because abs(13-14)=1, which is less than
2.
....
the next third number is 3, abs(13-3)=10, abs(8-3)=5, which is bigger than 2
[13 8 3]
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
the next forth number is 23, which is OK, abs(13-23)=10, abs(8-23)=15, which
is bigger than 2
[13 8 3 23]
13, 8, 7, 7, 14, 14, 14, 3, 3, 3, 23, 19, 2, 2
the next fifth number is 19, abs(13-19)=6, abs(8-19)=11, abs(23-19)=4,
abs(3-19)=16
[13 8 3 23 19] DONE, which values are stored in cell B1 to B5
Does any have any suggestion?
Thank you very much in advance
Eric
|