I need to set ranges for Unique values, then from those ranges pick random cells...

Below is a sample (Example) of the data (3 columns (in case it doesn't show properly)), The amount of data will vary.


- PERSON NAME - - REGION - - TICKET NUMBER -


PERSON 1 AsiaPac 31964190
PERSON 1 AsiaPac 31964221
PERSON 1 AsiaPac 31964229
PERSON 2 AsiaPac 31962860
PERSON 2 AsiaPac 31963051
PERSON 2 AsiaPac 31963093
PERSON 3 AsiaPac 31963107
PERSON 3 AsiaPac 31963352
PERSON 3 AsiaPac 31963476
PERSON 4 AsiaPac 31964154
PERSON 4 AsiaPac 31964166
PERSON 4 AsiaPac 31964176
PERSON 5 AsiaPac 31963325
PERSON 5 AsiaPac 31963330
PERSON 5 AsiaPac 31963382
PERSON 6 AsiaPac 31963021
PERSON 6 AsiaPac 31963155
PERSON 6 AsiaPac 31963327



I need to set ranges for each unique person, then pick 10 random numbers from the 3rd colum (always C) in each of the unique ranges. (Obviously there are only 3 for each above...but there would usually be WAY in excess of 10, however I guess I need to work out how to account for the posiblity that there may be less on some occasions). Also, the number of people will vary too.

I'd like have the data copied into the 4th row (always D), PERSON NAME, then a list of the numbers picked for them, then the 2nd PERSON NAME...

PERSON 1
31964190
31964221
PERSON 2
31962860
31963051
PERSON 3
31963107
31963352
PERSON 4
31964154
31964166
PERSON 5
31963325
31963330
PERSON 6
31963021
31963155


I hope this doesn't prove too complicated, and i'm desperate to learn VBA so if anyone has the time to explain their code examples, I'd would be forever grateful.

Extreme thanks in advance.

Could you provide a quick excel file example so we can see formatting and everything?
Also, when you say unique ranges for each person does that mean that no 2 people's ranges overlap?
Can there be duplicates used or should each number be used only once?
How are the unique ranges set or identified for each person?

Originally Posted by Hawkeye16

Could you provide a quick excel file example so we can see formatting and everything?
Also, when you say unique ranges for each person does that mean that no 2 people's ranges overlap?
Can there be duplicates used or should each number be used only once?
How are the unique ranges set or identified for each person?

Sure thing Hawkeye16, i'm sorry, it's sometimes tough to know what information is needed, without Info Overload....


Here is an example with a little more data.
EXAMPLE.xlsx

What I mean by Unique ranges...
The numbers to the right of "PERSON 1", "PERSON 2" etc... are tickets they've logged. I'm trying to choose 10 for each person at random, and list them in a row, PERSON NAME then 10 of their tickets, then the next persons name, and 10 of their tickets and so on untill each person has 10 of their tickets picked at random, a continuous list is fine.

The list will be used to decide which tickets (numbers) should be reviewed, therefore each number should be used only once.

The problems are that the number of people, and the number of tickets in total will change each time this is used. Usually we're choosing from between 8-10 people and we're always looking for 10 random tickets from each person (except where someone doesn't have 10 logged tickets (rare, but possible)).

I'm sorry, but i'm not sure what you mean with the last question. The macro will be run on the data as presented in sheet 1 of the example.

the data is filtered and pulled from a MASSIVE sheet, but although the amount of data may change, the format of it, will always be as in the example.

Hope that clarify's my request.

Try this:-
NB:- If the the number of tickets for any one person is <= 10 then those number will be repeated in column "D", else 10 random numbers from the individuals list will be selected.

Please Login or Register  to view this content.

Regards Mick

Code modified to give results as specified:-

Please Login or Register  to view this content.

Regards Mick

Originally Posted by MickG

Code modified to give results as specified:-

Please Login or Register  to view this content.

Regards Mick

Nice one Mick, I'll test this later tonight (I work Nights, YAY ME!) and let you know how it goes.

Many thanks for your response though, regardless of the outcome.