Select Only If No Other Cell is Greater Than X and All Other Conditions are Met

Hi Gos-C,

Before trying to do this in a macro, I thought I'd try with a formula or two. If you have a look at columns EH:EJ, you will see that I have the 13 RDs noted.

Note: I had to tweek EC6 so that I had more than 13.

I haven't applied my mind to what to do if you have less than 13 yet - that is much more complicated.

Gos-C P76401083_01Jan201531Mar2015.xlsm

Is what I have done on the right track?

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hi Gos-C,

I can see that you are far more advanced than I am with VBA code, and I struggled to see what was going on, but here is a clue.

I did two things with you code -
1. I stopped the macro which fired every time I clicked in column "EG". It was driving me nuts! Just an Exit Sub line at the beginning.
2. I made some changes to the Random macro so that it only selected the >10 values. (It was not looking for that and including some values < $10 when there were values over $10 not getting the RD flag. I just made the array from "EC" instead of "ED" and added a condition for >$10.

I couldn't work out why the macro behave when you changed some parameters. For example, if you selected an amount in RD_REQUIRED which was less that the "qualifying records" in worked correctly, but if you enter, say, 16 - the thing puts "RD" into every cell. This is obviously the scenario that requires selecting some values less than $10.

I have included a macro (RANKSEL) in Module1. It receives the number of value to find < $10. (I manually entered c=3)
It then looks at those records that are less that $10 and uses the RANK function to pick up the highest values.

I write the result into columns EH:EI then clear them when done.

This may be clumsy, but hopefully you will find something to use.



I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hi Gos-C,

Too cold to be out on the farm today, so I thought I'd try to delve into the code you have been given.

GOOD NEWS - I found the culprit!

I've added one line of code - If dblMax = 1 Then dblMax = 0.999999999. That stopped the thing from writting RD to every cell if you only had, say 12 values which qualified.

I left the rest of the code alone, and added a small IF statement down the bottom which looks to see how many have been selected, and if it needs some more, it calls the little routine I sent you yesterday.

Gos-C Test 3.xlsm

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi Gos-C,

You're very welcome! Thanks for the Rep. Just your enthusiastic appreciation is thanks enough. Since there are a lot of miles between us, sitting down for a cup of coffee or something is a little difficult.

All the best - I enjoyed the challenge! I think I learnt a lot from the code DonkeyOte provided.

David