Code to randomize within a filter

[ScottBeatty]

I was presented a scenario from my boss and was wondering if somebody could help me out. My sample spreadsheet attached has only 1,500 records but the actual one has about 10,000 records and was wondering if there was a code to help me reduce my sample.

I was asked to find a way to find a way where I could randomly select 2 records for each client. The catch is that the 2 records have to be different states.

To further complicate things, I have to do this once a month and for each month, I need records on states that have yet to be selected until all 50 states are selected, and then the process starts over again.

I was wondering if there was a code that could me with at least some of this process but any ideas, suggestions, comments, anything would be greatly appreciated. I don't know where to start.

Thanks!
Scott

[nilem]

Hi Scott,
maybe something like this

Please Login or Register  to view this content.

[ScottBeatty]

Now does that run automatically or would I have to run a macro/create a button?

Also the spreadsheet I'm working with has the same objective, but column A in my sample spreadsheet is column J and my column B in the sample is column F in my company spreadsheet. How much does that change the code?

[nilem]

here is your file with a button

[ScottBeatty]

Oh awesome! Thank you. I have another question for you. Is there a way to clear the contents and then click the same button to yield more results. So after the first month I would could repeat the process.

Also, is that randomized because after I clicked the button almost every 2 records for each client was AK and CA. Either that's a crazy coincidence but it seems almost alphabetical.

[nilem]

then let's try this

Please Login or Register  to view this content.

[ScottBeatty]

Oh awesome!! I just have a few more question about making some modifications. If I were to put the data onto another sheet? Would that change the code at all? Also, although I'm only using columns F and J, is it possible to transfer the entire row over to the new sheet?

Thank you!!

[nilem]

Scott,
without your example, I can only say that this is possible

[ScottBeatty]

Okay I'll make a sample spreadsheet for you and post it when I get back into work tomorrow. Thanks for all of your help today!

[ScottBeatty]

Hello,

so I attached a spreadsheet that isn't exactly the same as my actual one, but it's the same design. I was wondering if there was a way to put the data selected onto a new sheet, or at the very least have the data selected with the code already used, but move the entire row over to the gray box. I was really hoping to do this without using an Index/Match equation.

Thanks.

[ScottBeatty]

Actually, it would be better if I could just duplicate the record in a different area after pressing the button