How very clever! Yes, this worked! I did get three "each other" responses and I will study the code and try to figure out how it works. Thank you so much!
On Thu, Nov 14, 2013 at 4:05 AM, Brian Barker <[email protected]>wrote: > At 10:17 11/11/2013 -0500, Helen Etters wrote: > >> A LO calc question. It's a bit trivial, but I'm sure it can be done. >> Names of about 20 people. Draw names for Christmas gifts. >> > > Have you wondered why you have got little in the way of replies? Er, > perhaps "Draw names for Christmas gifts" is not a clear definition of a > problem! You have three presents to give away and you just want pick three > names as gold, silver, and bronze winners - right? Oh, - or you want to > divide the twenty into two groups, ten givers and ten receivers - right? > No? > > We can wait until we're all together in a couple of weeks, and that will >> work, ... >> > > I'm finding it difficult to understand how the subjects' physical presence > will simplify your task. Are you going to give them random numbers on > pieces of paper and ask them to do a dance to arrange themselves in order > by value? Is that called Musical Numbers? That's a sort of Christmas > party I'm not familiar with. > > ... but can we do this by running a random number generator in calc? >> > > Very probably. > > Type 20 names into column one, then the same names again in column 2, >> then run a random number generator to match them up? >> > > Ah, so you are somehow matching twenty with the same twenty. What does > that hint to us about your actual needs? Hmm, let's see now. Perhaps you > want each of the twenty to give a gift to another of the twenty - and to > randomise the results. Is that it? > > Well, if you randomise the order of one column, as you are perhaps > suggesting, it is possible (quite likely?) that someone will be selected to > give their gift to themselves; is that OK? Perhaps not. Let's try to > avoid that and get everyone giving to someone else. But now we may have > another problem. In general, each person will give to and receive from > different people, but it may happen that the same people are chosen both > ways - so that one pair of individuals simply exchange presents both ways. > Does that matter? Do you see your problem needs more careful > specification? > > I did something similar to this about a year ago (for a church dinner) >> but for the life of me I just can't remember how I did it, and the >> instructions I get by googling don't work. (When I follow those >> instructions, the function returns #NAME? in the cell.) >> > > It's not much help to know that unless you explain what the instructions > were. > > There ought to be a neat way to do this, but I don't immediately see one. > Try this slightly messy one: > > o Enter the names into column A. I'm assuming there are twenty. > > o Copy these into column B. (Don't retype or you may create differences.) > > o In C1, enter =RAND() and fill this down column C. > > o In D1, enter =IF(A1=B1;"Themself!";IF(VLOOKUP(B1;A$1:B$20;2;0)=A1;"Each > other!";"")) and fill this down column D. > > o Select the data in columns B and C, i.e. the range B1 to C20. > > o Go to Data | Sort... . For "Sort by", select "Column C". (It doesn't > matter whether you choose Ascending or Descending.) > > o Click OK. > > o The names in column B have now been randomised, but you will probably > find that column D shows either some people selected to give to themself or > some selected to give to each other. If so, go to Data | Sort... | OK > repeatedly until column D is blank and shows no problems. Each time you > sort, the random numbers are recalculated and the order in column B > changes. For twenty names, you may need a few sorts to clear errors but > you should get there fairly quickly. > > o Print columns A and B. (Set a print range for convenience.) > > If you need too many sorts to achieve a result, lose some friends. > > I trust this helps. > > Brian Barker > > > -- > To unsubscribe e-mail to: [email protected] > Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to- > unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be > deleted > > -- Helen Etters using Linux, suse12.3 -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
