Randomizing Items in a List

[briand907]

1. I have a list of items, say a deck of 52 cards.
How do I "shuffle" and report the resulting order?

2. If I have 13 items (cards), how do I create a list of 39 items that does not include these 13?

[Ranelagh]

Hi Brian,

This is an excel question right?!

If you have a list of 52 items, you can put =RAND() beside each item. This gives an integer between 0 and 1. Sort based on the RAND column and it will "shuffle" your deck.

For the second part if you have the full 52 list, you can use a vlookup from the 52 to the 13. You'll end up with 39 #N/A cells which are your missing list.

[briand907]

Thank you!
Yes, this is an Excel question.

Your answer to #1 was perfect.

Re: #2,
I put the numbers 1 to 52 in a column (a1:a52)
I put 13 numbers in column B
(e.g. 13,11,10,27,26,17,15,39,37,50,49,45,43)
I put VLOOKUP(A1,b$1:b$52,1,false) in column C and copied it down 52 times. This returns #N/A when a number in A is not found in B.

Great. But I can't use this information. For example, I would like to put a formula in column D which says: IF(C1=#N/A,A1,"")

But that won't work.
Am I missing something?

[Ranelagh]

In Column D use this

=IF(ISERROR(C1),A1,"")

[briand907]

Thanks so much!

[Cutter]

@ briand907

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[briand907]

I asked previously about random sorting in Excel.
E.G. I have a list, 1 to 10 and I want to randomize the order.

I was told to put =rand() in the cell beside each number, and then sort based on the column of =rand()s.
This works on a one-time basis, but I want it to re-randomize each time the spreadsheet recalculates.

Am I missing something?