Random Sample w/Priority

Hello, i am looking for a 'best method' for this situation...(file attached).

The goal is to take ONE random sample for each unique Customer #.
However, 'Cars' take priority over other purchases (example: If the random purchase sample was an apple but the same customer# also bought a car, the random sample becomes the car).

My approach was going to be to copy a random customer# from the list to a new worksheet, then loop thru the original rows deleting matching Customer#'s while also checking for the car purchase. IF the car was purchased it would replace the copied row. in the end id be left with a unique list of customer#s, with all unique car purchases where applicable.


Can anyone think of a simpler solution for this type of scenario?

Try this for results on sheet2 starting "A1".

Please Login or Register  to view this content.

Regards Mick

@MickG - this works great. Unfortunately for me i have never seen RAY used & am finding trouble following(stepping doesnt show results until complete).
Can the dictionary be substituted for a simple 'remove duplicates" column in worksheet 2?.
Does the RAY method go hand in hand with .keys/dictionary?
...i'm just trying to dumb down your code for a person like me to troubleshoot if need be.

I've removed the Array "Ray" and added some notes.
If you step through it you should see what's happening !!
Hope that helps.

Please Login or Register  to view this content.

Regards Mick

Another option , No Dictionary and No array, Plus direct update on sheet2 !!

Please Login or Register  to view this content.

Regards Mick

@Mick - Thank you very much these have all been helpful!

Mick - If i were to need the randomized item VALUE returned (instead of "Car"), does that throw a wrench in this code?

You're welcome !!
Try this, its got a msgbox for selection for "Car" or "Random item".
NB:- If you want it in another code let me know !!

Please Login or Register  to view this content.

Regards Mick

Mick - Thanks for the quick reply. Just to confirm, when i choose random it is likely that a Car purchase is going to end up an Apple/Banana correct?

Just to clarify my original question... if you were to edit the list of items purchased and number the "Car"s (Car1, Car2, Car3)... the randomized result would have the specific value of 'K'?

im re-attaching the file for a better explanation... (ive added multiple Cars and numbered them)
My question was in regards to identifying the serialized Car, rather than just "Car".

So if you Select "Car = "Yes" " from the msgbox do you want to return a random car from those available, or something else ???

i believe my answer would be yes to a "random car from those available". Im just trying to differentiate the Cars purchased in this example.
(so if customer 22 purchase 5 cars, i need the result to be a specific car 1 thru 5)

Try this:-
If you select "Car= Yes" then the returned value will be a single random car from those available for that customer.

Please Login or Register  to view this content.

Regards Mick

These are all excellent examples to my questions. Thank you again MickG!

You're welcome