Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to do this kind of sorting?

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting to get the latest of a kind varkeychaya Excel Worksheet Functions 2 July 20th 06 04:43 PM
Sorting Sorting Sorting Skydiver Excel Discussion (Misc queries) 4 June 3rd 06 02:42 PM
sorting non contiguous ranges gsh20 Excel Discussion (Misc queries) 1 September 8th 05 04:50 PM
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . agc1234 Excel Discussion (Misc queries) 6 May 26th 05 08:02 PM
sorting number in ascending order Janice Lee via OfficeKB.com Excel Discussion (Misc queries) 2 April 8th 05 10:31 PM


All times are GMT +1. The time now is 06:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"