Solutioning How to Do a Group Pairing with No Duplicates Given a Top Picks List
-Microsoft Office Home and Student 2021
-Windows Desktop
-Beginner
-Formula preferred, but willing to try Power Query (never used) or Macros/VBA (used exactly once for something very basic) if there's a detailed explanation/resources < I may not be able to verify a solution as it is entirely possible this is both beyond my skill and excel is not the correct tool.
-This is something that occurs yearly and the amount of data points varies greatly, so hoping for something dynamic that can be adjusted to fit the data year to year.
The real-life data points for my scenario contain personal information so I'll be substituting with a (slightly ridiculous) data set. The example does match in terms of it all being text based. (I did consider assigning numbers, but wasn't sure which array that should be applicable to if that's the best path forward.)
Yearly there is an event run that I have recently become involved in, and I'm trying to streamline a pairing process. The event has a selection of unique fruits available. There is only one of each fruit type and each year the number of fruits available varies.
There are individuals who want one piece of fruit. The number of individuals who want fruit each year vary - they could be more, less or equal in number to the amount of fruit pieces available.
As individuals have preferences for what kind of fruits they like, the event runners ask that the individuals provide a list of their top picks - they could be paired with any of the picks on the list. The number of picks requested by the event runners would vary based on overall volume. e.g., if participation was around 100, runners might ask for 10 top picks. If around 30 the ask would be for 5 top picks.
With these criteria/variables then, I need to pair each individual with a fruit on their pick list with no duplicates. e.g., if two individuals choose Watermelon, only one can be assigned the Watermelon, and the other individual will need to be paired with a different fruit from their pick list - while also keeping in mind that the other fruit this second individual is being assigned then cannot also be assigned to any other individual who may have selected that other fruit.
The difficulties of this being -
- It's possible one or more fruits were not selected by any individual and so there are less fruits than individuals available.
- If there are less fruits than individuals from the start, and not every individual will get a fruit.
- If there were less individuals than fruits, so not every fruit will be selected, leading to waste.
Given the volume that will change from year to year and how random the pick lists will naturally be year to year, it is very likely that, mathematically, it is not possible that every individual and fruit can be successfully and uniquely paired.
The goal is to match as many individuals to fruits as possible, given the restrictions of the pick lists. Then, any fruits and individuals that could not be paired are "set aside".
(From a real world perspective, once all the possible matches are made, the event runners would reach out to the individuals that couldn't be paired and discuss options in terms of the remaining unassigned fruit, doing their best to see if the "left overs" can be picked up, expressing regrets about not being able to get them a fruit, or asking if they'd like to share a fruit with someone else.)
The data comes in with an individual's name having the fruits they picked under it. Easy enough to transpose and assign headers 'Claimant Name', 'Pick 1', 'Pick 2', etc.
I did also figure out how to get the data rearranged so the fruits are the header row with the name of each individual who claimed it, and what number pick it was for that individual under the fruit using FILTER.
So I've got the data listed a few ways, and can always add some filter drop downs on the fruit header row and start going through manually looking for fruits that only had one top pick, pairing them, and then manually removing that individual's name from under any other fruit where it appears so it can't be considered, avoiding duplicates. But that way lays hours of work, fraught with errors and migraines.
What would be the way to approach creating a dynamic method to do this pairing that can be applied in different years given the restrictions of the pick list and varying numbers of fruits and individuals involved?
[link] [comments]
Want to read more?
Check out the full article on the original site