Forum Discussion
Solved
Need help with Formula to help place campers into classes based on top 5 choices selected
Hi,
We run a week-long summer camp for burn survivors and every year we go through and assign campers to 3 different classes throughout the week based on their top 5 choices picked. We typically have 60 -70 campers and this process takes a long time. I was hoping there was a way in excel to do this more easily. I attached an example of the report that is generated from the camp software we use when they register for camp. At least I hope I correctly posted the link. Any help would be greatly appreciated!
OK, see attached file:
- Formatted your data as Table
- Added "ID" as 1st column header
- Put together 2 Get & Transform (aka Power Query) queries
- PEG: What I earlier shared
- Other Choices: Choices 4 & 5
With your data up to date in the Table (currently in sheet 'report (46)'),
- Switch to sheet 'PEGs'
- In Excel Data tab > Refresh All (takes a couple of secs.)
Any question let me know
Hi CaitlinHBC
Your link works, a couple of question though:
- Could you give a couple of examples of what's expected in the PEG columns?
- What version of Excel do you run + on Windows or Mac?
- The PEGs are the classes they choose, so ideally I would love for it to say, "swimming" or "archery" (the PEG name in the column). Some of these PEGs happen at the same time, which we don't have information for yet, but can figure that out later. Below is our typical "Human Computer" process when we assign these PEGs:
1. Designate when PEGs will be (1, 2 or 3)
2. Go through each list marking 1st preference PEG
3. Go through each list marking 2nd preference PEG
4. Go through each list marking 3rd preference PEG
5. When doing this, try to pay attention to PEG 1, 2 and 3 and the time offered for potential conflicts
I'm running Excel on Windows and it says the version is 2402Probably not what you expect as I don't understand what to do with choices 4 & 5 (I must be slow today). Does the following +/- reflect what you want (based on the data you shared)?
