Hi.
I need help to automate this peace of work with VBA code. Hope someone will help me.
Links to screenshot if it not appear
http://img15.imgspot.com/u/05/353/16...1135113951.gif
mirror
http://img430.imageshack.us/img430/9...itled215ck.gif
The source range is B2:K46 (45 rows) and the range of second block where the results must appear is M2:V46 (already with results on picture).
Generally I need to find 5 smallest numbers in every row and get them as value 1 in matching rows of second block. Non smallest numbers - value 0.
But it happens not always when the number of smallest numbers is 5. Bellow I described criteria for all possible cases. For better understanding I marked in red color the smallest numbers which are valid and will get value 1 (I will call them primary smallest numbers), and in blue color - numbers which are not valid because together with primary smallest numbers number of them reaches over 5 (I will call them secondary smallest numbers). They will get value 0.
Case 1 . An ideal case - 5 smallest numbers.
Case 2 . In this case we have four primary smallest numbers 1,2,2,3. Can't add one more because the next in order are two (or more) the same numbers (4 and 4)
Case 3 . Three primary smallest numbers 3,4,4. Can't add two more because the next three (or more) are the same (6,6,6).
Case 4 . Two primary smallest numbers 1,2. Can't add three more because the
next four (or more) are the same (3,3,3,3).
Case 5 . The rule changes here. Only one smallest number (3) and five next in order numbers (4,4,4,4,4). They all six will get value 1.
Case 6 . Only one smallest number (6) and six next in order numbers (7,7,7,7,7,7). All seven will get value 1.
Cases 7,8,9 . The rule turns back. If the smallest number is unique and more than six next in order numbers are the same, then only that one smallest will get value 1.
Cases 10,11,12,13,14 . More than five the same smallest numbers. All will get value 1.
Thanks.
Bookmarks