shuffled deck of cards

[David Obeid]

I have the names of the 52 different playing cards listed in cells H1:H52 written out in "order" (ie all of the hearts, from Ace to King, followed by all of the diamonds from Ace to king etc).

Is there a way to reproduce the list so that the deck is shuffled?

ie all 52 cards appear in radom order in Column A from A1 to A52?

Thanks in advance.

[martindwilson]

yep !
an addin from asap utilities includes a randomise function
select column then from range in drop down select 'randomise list'
http://www.asap-utilities.com/

[David Obeid]

Thankyou.

I'll do that, but is there a (non-VBA) way to do it with excel's own functions?

[mikerickson]

I think the attached will do it.
Row 1 is headers
A2 is blank
A3:A54 are the 52 cards "C 1","C 2", ..., "s 13"

In B2, put
=INT(RAND()*52)+1
and fill right to BA2

In B3, put
=IF(ROWS($B$3:B3)=COLUMNS($B$3:B3),INDEX(A$3:A$54,B$2,1),IF(ROWS($B$3:B3)=B$2,INDEX(A$3:A$54,COLUMNS($B$3:B3),1),A3))
Select B3:BA54, fill right and down.

The column BA3:BA54 is the shuffled deck.

What is happening is that, in the i'th column, a random number between 1-52 is being selected and the i'th card is being swapped with the random'th card.
52 columns shuffles the whole deck.

One could hide the 51 helper columns. This may be a case where VB is better than native Excel.

[David Obeid]

That's great!

Can I push my luck and ask another question whilst I am at it?

Is there a simple (ie non-VBA) way to get excel to work out what the first and second last characters in a cell are? Say I have "2 Black ducks" in A1 and "Johnny went to town" in A2, I would like the 1st character of A1 in B1 and the second last character of A1 in C1, likewise for what's in A2 to go into B2 and C2.

Thanks again.

[mikerickson]

If "Johnny went to town" is in A1,

=LEFT(A1,1) returns the first character of A1, "J"

=MID(A1,LEN(A1)-1,1) returns the second to last character of A1, "w"

=RIGHT(A1,2) returns the last 2 characters of A1, "wn"

The other text functions can be found through the Insert Function dialog box.

[David Obeid]

Dynamite!

This is a very helpful forum!

Thanks a million!

[bevis]

aaaaaaaaaaaaaaaaaaaaa

[dominicb]

Welcome to the forum, bevis!

Please don't post a question in the thread of another member -- just start your own thread.

If you feel that this thread is particularly relevant to your need, provide a link to here in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

DominicB

[bevis]

It should tell you that when you sign up.

[AliGW]

When you sign up, you are expected to read the rules before posting (there's a clear link to them at the top of this page):

Forum Rules

Please read and adhere to these simple rules!

1. Title must briefly summarize your request. (A, Z)

2. Programming code must be enclosed in code tags to improve readability. (A, Z)

Please Login or Register  to view this content.

(or use the # button)

3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked. (A, Z*)

4. Do not post a new help request in an existing thread. (B)

5. Do not open more than ONE thread per issue here. Expect duplicates to be closed. (B)

6. Do not post to threads where a moderator is addressing a rule violation. (B, C, Z*)

7. Do not inquire about breaking the security of a protected workbook. (B, E)

8. Do not private message forum moderators or members for Excel help. (A, Z)

9. Abusive language or insults will not be tolerated. (B, Z)

10. Do not put links to commercial sites or services or to competing forums in signatures and posts. (A, B)

11. Do not put links to personal sites or services as your sole contribution here on the forum. (B)

12. Do not solicit payments in your posts or signatures. (B)

13. Only give reps to members who earned them, not just for something trivial like an acknowledgment. (B, F)

[bevis]

okjdrtjdrndndtynytdnty

[AliGW]

Please go to your user profile and update your location (e.g. Europe, UK, USA, etc.) and your Office version (e.g. 2010, 2016, 365, etc.), as both are used by helpers when tailoring their answers to your queries. Thanks.

[bevis]

Ok Miss fussy

[bevis]

Please do not reply to my previous post

[AliGW]

Five posts and you are not making a great first impression. I would be very careful what you say if you wish to remain a member here. Whether or not you take my advice is up to you, but the request for extra details is for your benefit, not mine.

I suggest you have a good read through those forum rules, especially rule #9, before posting again. Thank you.

[bevis]

Probably shouldn't ask you this but please can you delete my account?

[AliGW]

No, unfortunately not. You would need to ask an administrator.