Excel Macro for Progressive Dinner

Topic

New Reply

I would like an Excel Macro for the following scenario:

There are 200 people attending a Progressive Dinner. There are 20 cocktail hosts and 15 dinner hosts. Assign a linear number (1-200) for each of the participants. Generate a set of random numbers for each of the 200 participants with a range of 20 for the cocktail reception and with a range of 15 for the dinner. These random numbers are associated with each linear number (1-200) for the 200 participants.

Then create a set of columns for each of the cocktail hosts and dinner hosts. Under these columns, list the linear number of the participants who would be assigned to each of the cocktail and dinner hosts based upon the random number generators. I hope all of this makes sense.

If you need clarification about this whole thing, please contact me. Be sure to take NoSpam out of my e-mail address.

TIA,

Dick

Reply | Quote

Replies

I think the attached file will do what you want – obviously you will have to input the real guest names and cocktail / dinner locations to replace the sample ones I used.

Each guest is assigned two random numbers, one for the cocktail location, one for the dinner location – other wise you wouldn’t get any mingling of the groups. The “Rank()” function is used to determine the ordinal number of each guest’s random number among the set of all random numbers assigned. A look-up table then associates the ordinal number with a cocktail location, by assigning the 10 lowest ordinals to the first location, the next ten to the second location, and so on. The same procedure is used to assign dinner locations.

It would be possible to list the guests in order by location by sorting or filtering the guest list by location, but it was just as easy to use the two sets of data as the base for a pivot table. The two pivot tables are on sheet 2 – highlighting either the “Cocktail Location” or “Dinner Location” buttons and clicking the “+” sign or “-” sign on the Pivot Table toolbar will allow you to toggle back and forth between a summary view (lists number of guests per location) and a detail view (lists the guests by location).

Hope that helps!

Reply | Quote

Okay – so where was the file the last time? Maybe second time lucky?

Reply | Quote

Hi Dean,

Thank you very much for the spreadsheet. I may not have explained the situation well enough. The cocktail hosts are different from the dinner hosts. The selection for both dinner and cocktail must be independent (as I believe you’ve done). I believe you’ve set it up so the host is added to his own function.

Is it possible to leave names out and just have a variable number of total attendees input into a cell and have that be represented by a set of linear numbers? Also, is it possible to have the number of cocktail hosts input as a variable number into a cell? And finally, have a variable number for dinner hosts input into a cell? Then do the linear number set, random number sets, and then the pivot tables from the three variables that were input? From year to year, we don’t know how many will attend, how many cocktail hosts will volunteer, and how many dinner hosts will volunteer.

I hope this isn’t asking for too much.

Thanks again for your help, Dean.

Best regards,

Dick

Richard.NoSpam.Copple@Usertech.com

Reply | Quote

Dick – I think the S/sheet will work for you as it is. The names of the Cocktail and Dinner hosts were arbitrary (you can see my limited imagination by the fact that I couldn’t come up with other names). If you simply replace the host names shown with the real ones you have, you will generate the lists of guest for each host appropriately.

I didn’t put in anything to force host to be included at their own function – in fact, the sheet logic implies that hosts are distinct from guests – they do all the work and don’t even get invited to the party!

Since the host for -say- a cocktail location will also be a participant in the dinner, but NOT in the cocktails (ie – they will not be a guest at anyone else’s cocktail location) I would ‘force’ the random number for cocktail assignments for all of the hosts to be less than zero. This will put them all at the back end of the list of ordinals – then set the last “N” ordinals (where N = number of hosts) to a dummy host since you don’t need to assign those people to a host – all those folks will already be at their own functions. You then do the same thing for the dinner random numbers and hosts.

It is possible to automate this process if required, although there may be some manual interventions required. I will post a revised S/sheet in a little while.

Drop me a note if this is all clear like mud!

Reply | Quote

Hi Dean,

Thanks for the update. In this scenario, the cocktail hosts will definitely attend a dinner. The dinner hosts will definitely attend a cocktail. Since we really don’t want to input any names, just the total number of participants, is it possible to have the three variables input and have the spreadsheet generate the number of total guests, set up the pivot table for the number of cocktails and the pivot table for the number of dinners? In your first spreadsheet, I did not see a table of cocktail hosts with their guests. I did see the table of dinner hosts with their guests. Am I missing something here? And to answer your question, this whole thing is quite murky at this point! As I’m not a VBA programmer at all! More of a hardware and tech support type!

Thanks,

Dick

Reply | Quote

Hi Dick

I have attached a revised s/sheet. It determines the number of “names” of cocktail and dinner hosts and tests whether the each name in the list of guests is included in the list of hosts. If the particular name is a ‘host’ for that portion of the evening it assigns a negative number to it rather than a random number. Since the ordinals are determined from largest to smallest, it forces the ordinal to be in a “tie for last place.” The number of ordinals in last place are not assigned a location.

To use the S/sheet, place the “names” of guests in the sheet in the range from B31 and downwards for however many rows you need. Note that there should be no blank rows before the end of the block of names, and that the names have to be in the range named “Guests.” I can appreciate that it will be a pain to actually use full names, so you could do it by assigning arbitrary numbers, from 1 through the number of participants, and just filling the range with those numbers.

In the ‘host’ locations ranges (F4:G24 and I4:J24) you fill in the host names for cocktails and dinner – and they must match the names in the ‘guest’ list exactly (numbers make that easier!). It is the match to the name in the ‘host’ ranges that identifies them in the guest range as hosts and forces the random value to be negative. Again, clear like mud, I’m sure.

To get the pivot table to show the list of guests you either click once on the “cocktail location” or “dinner location” buttons on the pivot table and then on the yellow “+” on the Pivot Table toolbar (I am using XL 2000, but I remember the toolbar coming up automatically in XL 97 when a Pivot Table is activated – I can’t remember if it popped up in XL v. 5) to show the guests or on the “-” to hide them. Alternatively, if you want to show the guests for one specific host, click once on the host name in the pivot table, and then on the “+” or “-” buttons on the PT Toolbar.

PS – I am doubtful that a hardware and tech support guy would have 200 friends – are you sure you’re not putting us on!?!

Reply | Quote

Hi Dean,

Thanks for the updated spreadsheet. I’ll try it out tonight if I have time. Must keep in touch with my 200+ friends, you know. Does the spreadsheet allow for the entry of a variable number of cocktail and dinner hosts?

Thanks again,

Dick

Reply | Quote

Hi Dick (and 200 of his closest personal friends):

As it sits now you can “use” a variable number of hosts by only putting the required number in the “locations” ranges and leaving the rows below blank. It will probably be a little clearer after you look at the s/sheet.

I am giving some thought to how you might just put in the required variables and have some VBA code to spit out the guests (by number) per location (also by number) but I am having a tough time with how you would identify that guest (or perhaps “participant”) number 123 was a host, but #124 was not. At this point, it is a conceptual problem: I haven’t even begun to think about how to code it!

As a practical matter – isn’t it going to be just about as big a problem to identify to everyone involved that “participant 45” is actually Kathleen Cardno, and “host 16” is Doug Banks, etc, as to type in the names. I presume that somebody has a list of names somewhere that could be cut/pasted into place….

Reply | Quote

Hi Dean,

I suppose you’re right about inputing the names. We already use a spreadsheet to track the participants with phone number and status regarding host, etc.

Thanks,

Dick

Reply | Quote

Dick:

So – I’ve finally finished the macro you asked for (rather than the s/sheet I told you you needed – hey, I’m a consultant: ya sell what ya got!). If my luck holds,you have already finished with your dinner, but it was an interesting way for me to learn a couple of things! In the s/sheet you can enter the number of participants, the number of cocktail and dinner hosts, press the button (still imaginatively labelled ‘button 1’ and it will generate a list of the guests assigned to each host, by number. I have arbitrarily decided that if you have X cocktail hosts and Y dinner hosts, they will be the first X participants and the last Y participants, respectively.

In truth, I still think this is a less-than-optimal solution, since you now have to generate a concordance that will tell Nancy Adams that she is participant number 52, and that host number 3 is Bill Smith and host 198 is Drew Jones, etc. But hey, it was interesting to work on!

There is a limit to the number of participants that can be accomodated: in order to use the “RANK” s/sheet function I had to dump a list of random numbers (one per participant) into the files, and in order to get the function to work it had to be a horizontal list, so you can’t have more participants than the number of columns in the s/sheet, ie – no more than 256 people involved. I am going to be sending out a separate query to the group here about both those limitations, but I think this conversation is pretty stale, so I will start a new thread!

Cheers – I hope the dinner goes well,

Dean

Reply | Quote