Randomly select between two dates

[MagicMan]

Hi Folks,

In March, 6StringJazzer kindly provided me with a solution for randomly selecting a record when the month within a date matches (See Here)

However, I've been asked if I can take this one step further and see if it can't be refined between two dates. For example the 'low date' entered in cell B1 and the 'high date' entered in cell C1 ,(I can change the size).

Then, on activating the macro (through pressing the 'Go' button) it selects a name within (but including) those parameters.

I've attached the spreadsheet (with Macro) developed by 6StringJazzer. Any help will be greatly appreciated.

Please don't hesitate to get in touch if you need any further clarity.

Kindest Regards

MagicMan

[shukla.ankur281190]

Try

D3

Please Login or Register  to view this content.

It will give you random names under start to end dates.

[MagicMan]

Thanks Shukla,

I tried inserting that into my spreadsheet and it didn't work for me. Maybe I'm doing something wrong. I'd be grateful if you could post it in the spreadsheet and upload it so I can see where I may be going wrong.

MagicMan

[shukla.ankur281190]

May be you have not given dates that is why its happening.

Check the attached file.

[MagicMan]

Sorry Shukla, I'm afraid not.

When I enter two new dates, it goes completely random and selects from anywhere on the list.

I thank you for your efforts though

[shukla.ankur281190]

The names will be between those dates which you will give in cell B1 ( Greater than and equal to B1) & C1 (Less than equal to C1) and when you will click on your button it will take random name which you want??

Am I wrong ????

[MagicMan]

Yip!! That's what I'm after (if the existing macro is causing problems, feel free to delete it).

[shukla.ankur281190]

What do you exactly want can you please speak... as per OP I have given you solution and it is working great...

[MagicMan]

And I do appreciate your efforts shukla.

I don't know if you have something enabled that I don't, and perhaps should, but when I enter dates in your solution, it doesn't restrict the selection to within those dates. For example, I selected dates between 02/03/17 and 06/03/17 and it returned "Marilyn West"

Sample 1.png

However, looking at the list, Marilyn West's entry was made on 30/01/2017

Sample 2.png

Whereas it should have been any one of those highlighted in yellow:

Sample 3.png

[shukla.ankur281190]

Sorry my mistake

try

Please Login or Register  to view this content.

Now check the attached file.

[MagicMan]

A-HA!!

I've found the problem. You're using the dates in 'List of Names'!A:A, I need to use the dates listed in 'List of Names'!D:D (some of which may be repeated)

[Phuocam]

Try this ...

=INDEX('List of Names'!E:E,SMALL(IF(('List of Names'!D1:D200>=B1)*('List of Names'!D1:D200<=C1),ROW(D1:D200)),
RANDBETWEEN(1,COUNTIFS('List of Names'!D1:D200,">="&B1,'List of Names'!D1:D200,"<="&C1))))

Enter with Ctrl+Shift+Enter.

[MagicMan]

Phuocam - Many thanks - got there in the end :-)

Shukla - Thank you for pointing us in the right direction :-)

Kindest Regards,

MagicMan