+ Reply to Thread
Results 1 to 5 of 5

Challenge to find unique matches without duplicates.

  1. 06-16-2021, 12:11 PM #1
    dnomyar105
    dnomyar105 is offline
    Registered User
    Join Date
    01-12-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    6

    Challenge to find unique matches without duplicates.

    I'm running a matchmaking service where Participants get 1 unique match per week. I have a filtered list of names of people who are good matches. The challenge is such that:

    Participants can only get 1 match; no duplicates. For example, Colleen Marks matches with Anh Vu. Therefore Anh Vu cannot match with Jorja Hester.

    Must Maximize the number of unique matches based on this data.

    I've attempted all kinds of sorting and replacement and cannot find an elegant solution to this problem. Hoping this community can help.

    File is attached.
    +-----------------+---------------------+
    | Match 1 | Match 2 |
    +-----------------+---------------------+
    | Colleen Marks | Anh Vu |
    +-----------------+---------------------+
    | Colleen Marks | Arron Carrillo |
    +-----------------+---------------------+
    | Colleen Marks | Jason Shamil |
    +-----------------+---------------------+
    | Diogo Robins | Ben Katzenstein |
    +-----------------+---------------------+
    | Franco Wallis | Jemma Lennon |
    +-----------------+---------------------+
    | Franco Wallis | Alex Coisman |
    +-----------------+---------------------+
    | Franco Wallis | Julien Rowley |
    +-----------------+---------------------+
    | Franco Wallis | Eduardo Flores |
    +-----------------+---------------------+
    | Franco Wallis | Edwin Yung |
    +-----------------+---------------------+
    | Franco Wallis | Arron Carrillo |
    +-----------------+---------------------+
    | Franco Wallis | Jonathan Shan |
    +-----------------+---------------------+
    | Franco Wallis | Jose Siade |
    +-----------------+---------------------+
    | Franco Wallis | Juan C. Méndez |
    +-----------------+---------------------+
    | Franco Wallis | Kason Francis |
    +-----------------+---------------------+
    | Franco Wallis | Karam Werner |
    +-----------------+---------------------+
    | Franco Wallis | Liam Nguyen |
    +-----------------+---------------------+
    | Franco Wallis | Hadiya Dodson |
    +-----------------+---------------------+
    | Franco Wallis | Nico Kline |
    +-----------------+---------------------+
    | Franco Wallis | Paul Tu |
    +-----------------+---------------------+
    | Franco Wallis | Randolph Capocasale |
    +-----------------+---------------------+
    | Franco Wallis | Sadia Sharmin |
    +-----------------+---------------------+
    | Franco Wallis | Sara Huang |
    +-----------------+---------------------+
    | Franco Wallis | Rhona Kinney |
    +-----------------+---------------------+
    | Jorja Hester | Jemma Lennon |
    +-----------------+---------------------+
    | Jorja Hester | Alex Coisman |
    +-----------------+---------------------+
    | Jorja Hester | Anh Vu |
    +-----------------+---------------------+
    | Jorja Hester | Bala Venkatrao |
    +-----------------+---------------------+
    | Jorja Hester | Julien Rowley |
    +-----------------+---------------------+
    | Jorja Hester | Eduardo Flores |
    +-----------------+---------------------+
    | Jorja Hester | Erach Desai |
    +-----------------+---------------------+
    | Jorja Hester | Arron Carrillo |
    +-----------------+---------------------+
    | Jorja Hester | Jason Shamil |
    +-----------------+---------------------+
    | Jorja Hester | Jonathan Shan |
    +-----------------+---------------------+
    | Jorja Hester | Jose Siade |
    +-----------------+---------------------+
    | Jorja Hester | Juan C. Méndez |
    +-----------------+---------------------+
    | Jorja Hester | Liam Nguyen |
    +-----------------+---------------------+
    | Jorja Hester | Hadiya Dodson |
    +-----------------+---------------------+
    | Jorja Hester | Nico Kline |
    +-----------------+---------------------+
    | Jorja Hester | Paul Tu |
    +-----------------+---------------------+
    | Jorja Hester | Randolph Capocasale |
    +-----------------+---------------------+
    | Jorja Hester | Sadia Sharmin |
    +-----------------+---------------------+
    | Jorja Hester | Sara Huang |
    +-----------------+---------------------+
    | Jorja Hester | Imaan Joseph |
    +-----------------+---------------------+
    | Jorja Hester | Rhona Kinney |
    +-----------------+---------------------+
    | Karim Underwood | Baron Cao |
    +-----------------+---------------------+
    | Karim Underwood | Julien Rowley |
    +-----------------+---------------------+
    | Karim Underwood | Arron Carrillo |
    +-----------------+---------------------+
    | Karim Underwood | Herbert Rwamibazi |
    +-----------------+---------------------+
    | Karim Underwood | Jason Shamil |
    +-----------------+---------------------+
    | Karim Underwood | Karam Werner |
    +-----------------+---------------------+
    | Karim Underwood | Myoo Nadesan |
    +-----------------+---------------------+
    | Karim Underwood | Raymond Shih |
    +-----------------+---------------------+
    | Karim Underwood | Sam Horn |
    +-----------------+---------------------+
    | Karim Underwood | Imaan Joseph |
    +-----------------+---------------------+
    | Karim Underwood | Shiva Venkatraman |
    +-----------------+---------------------+
    | Misty Bell | Julien Rowley |
    +-----------------+---------------------+
    | Misty Bell | Eduardo Flores |
    +-----------------+---------------------+
    | Misty Bell | Erach Desai |
    +-----------------+---------------------+
    | Misty Bell | Jose Siade |
    +-----------------+---------------------+
    | Misty Bell | Juan C. Méndez |
    +-----------------+---------------------+
    | Misty Bell | Karam Werner |
    +-----------------+---------------------+
    | Misty Bell | Liam Nguyen |
    +-----------------+---------------------+
    | Misty Bell | Paul Tu |
    +-----------------+---------------------+
    | Misty Bell | Randolph Capocasale |
    +-----------------+---------------------+
    | Misty Bell | Sadia Sharmin |
    +-----------------+---------------------+
    | Misty Bell | Sara Huang |
    +-----------------+---------------------+
    | Misty Bell | Rhona Kinney |
    +-----------------+---------------------+
    | Momina Lara | Alex Coisman |
    +-----------------+---------------------+
    | Momina Lara | Bala Venkatrao |
    +-----------------+---------------------+
    | Momina Lara | Julien Rowley |
    +-----------------+---------------------+
    | Momina Lara | Eduardo Flores |
    +-----------------+---------------------+
    | Momina Lara | Erach Desai |
    +-----------------+---------------------+
    | Momina Lara | Jonathan Shan |
    +-----------------+---------------------+
    | Momina Lara | Jose Siade |
    +-----------------+---------------------+
    | Momina Lara | Juan C. Méndez |
    +-----------------+---------------------+
    | Momina Lara | Kason Francis |
    +-----------------+---------------------+
    | Momina Lara | Liam Nguyen |
    +-----------------+---------------------+
    | Momina Lara | Hadiya Dodson |
    +-----------------+---------------------+
    | Momina Lara | Nico Kline |
    +-----------------+---------------------+
    | Momina Lara | Paul Tu |
    +-----------------+---------------------+
    | Momina Lara | Randolph Capocasale |
    +-----------------+---------------------+
    | Momina Lara | Sadia Sharmin |
    +-----------------+---------------------+
    | Momina Lara | Sara Huang |
    +-----------------+---------------------+
    | Momina Lara | Skyla Newman |
    +-----------------+---------------------+
    | Momina Lara | Rhona Kinney |
    +-----------------+---------------------+
    | Monika Hopkins | Alex Coisman |
    +-----------------+---------------------+
    | Monika Hopkins | Bala Venkatrao |
    +-----------------+---------------------+
    | Monika Hopkins | Eduardo Flores |
    +-----------------+---------------------+
    | Monika Hopkins | Erach Desai |
    +-----------------+---------------------+
    | Monika Hopkins | Jonathan Shan |
    +-----------------+---------------------+
    | Monika Hopkins | Jose Siade |
    +-----------------+---------------------+
    | Monika Hopkins | Juan C. Méndez |
    +-----------------+---------------------+
    | Monika Hopkins | Karam Werner |
    +-----------------+---------------------+
    | Monika Hopkins | Liam Nguyen |
    +-----------------+---------------------+
    | Monika Hopkins | Hadiya Dodson |
    +-----------------+---------------------+
    | Monika Hopkins | Paul Tu |
    +-----------------+---------------------+
    | Monika Hopkins | Randolph Capocasale |
    +-----------------+---------------------+
    | Monika Hopkins | Sadia Sharmin |
    +-----------------+---------------------+
    | Monika Hopkins | Sara Huang |
    +-----------------+---------------------+
    | Monika Hopkins | Rhona Kinney |
    +-----------------+---------------------+
    | Rayyan Harvey | Alex Devereux |
    +-----------------+---------------------+
    | Rayyan Harvey | Bala Venkatrao |
    +-----------------+---------------------+
    | Rayyan Harvey | Ben Katzenstein |
    +-----------------+---------------------+
    | Rayyan Harvey | Cathy Keeler |
    +-----------------+---------------------+
    | Rayyan Harvey | Julien Rowley |
    +-----------------+---------------------+
    | Rayyan Harvey | Eduardo Flores |
    +-----------------+---------------------+
    | Rayyan Harvey | Erach Desai |
    +-----------------+---------------------+
    | Rayyan Harvey | Jonathan Shan |
    +-----------------+---------------------+
    | Rayyan Harvey | Jose Siade |
    +-----------------+---------------------+
    | Rayyan Harvey | Juan C. Méndez |
    +-----------------+---------------------+
    | Rayyan Harvey | Karam Werner |
    +-----------------+---------------------+
    | Rayyan Harvey | Liam Nguyen |
    +-----------------+---------------------+
    | Rayyan Harvey | Hadiya Dodson |
    +-----------------+---------------------+
    | Rayyan Harvey | Paul Tu |
    +-----------------+---------------------+
    | Rayyan Harvey | Randolph Capocasale |
    +-----------------+---------------------+
    | Rayyan Harvey | Russell Davis |
    +-----------------+---------------------+
    | Rayyan Harvey | Sadia Sharmin |
    +-----------------+---------------------+
    | Rayyan Harvey | Sara Huang |
    +-----------------+---------------------+
    | Rayyan Harvey | Rhona Kinney |
    +-----------------+---------------------+
    | Zeshan Lowry | Alex Coisman |
    +-----------------+---------------------+
    | Zeshan Lowry | Bala Venkatrao |
    +-----------------+---------------------+
    | Zeshan Lowry | Julien Rowley |
    +-----------------+---------------------+
    | Zeshan Lowry | Eduardo Flores |
    +-----------------+---------------------+
    | Zeshan Lowry | Jonathan Shan |
    +-----------------+---------------------+
    | Zeshan Lowry | Jose Siade |
    +-----------------+---------------------+
    | Zeshan Lowry | Juan C. Méndez |
    +-----------------+---------------------+
    | Zeshan Lowry | Karam Werner |
    +-----------------+---------------------+
    | Zeshan Lowry | Liam Nguyen |
    +-----------------+---------------------+
    | Zeshan Lowry | Paul Tu |
    +-----------------+---------------------+
    | Zeshan Lowry | Randolph Capocasale |
    +-----------------+---------------------+
    | Zeshan Lowry | Sadia Sharmin |
    +-----------------+---------------------+
    | Zeshan Lowry | Rhona Kinney |
    +-----------------+---------------------+
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-16-2021, 12:21 PM #2
    cubangt
    cubangt is offline
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Challenge to find unique matches without duplicates.

    What have you tried? Are you looking for a formula or macro/function to help?
    I would think that once a match is made, you can flag those matches and exclude them from future matches within your list.
    Now with that scenario, those at the top of the list would obviously get the matches first and no one below would get Anh Vu unless they dont match anyone at the top, but that should be ok right?

    I would think that a macro/function would be the way to go so that you can build your list of matches and having a helper column ("Matched" set to TRUE or FALSE ) then as its making matches, if its false, then its ok to match with the appropriate person, if its true, then that person cant be used as a match and just moves on to the next.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.
    Reply With QuoteRegister To Reply
  3. 06-16-2021, 12:50 PM #3
    dnomyar105
    dnomyar105 is offline
    Registered User
    Join Date
    01-12-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Challenge to find unique matches without duplicates.

    The problem is that I don't think going with matches from the top down will result in the maximum possible matches for everyone in the list. For example, if Franco Wallis seems like a popular person so I should save him for later (because he's more flexible) and first match the hard to match people first? Does that make any sense.

    And yes, looking for a formula that is repeatable for future data sets in the same 2 column format.
    Last edited by dnomyar105; 06-16-2021 at 12:52 PM.
    Reply With QuoteRegister To Reply
  4. 06-16-2021, 01:52 PM #4
    cubangt
    cubangt is offline
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Challenge to find unique matches without duplicates.

    Not saying to change your selection process, merely add a helper column or flag of sorts to simply identify if a match has been made so that it cant be used as your logic continues to work. And if a simple Matched/Not Matched flag isnt enough, maybe a rating to identify the popular matches so that you can use that in the "match" process logic
    Reply With QuoteRegister To Reply
  5. 06-17-2021, 05:06 AM #5
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Challenge to find unique matches without duplicates.

    Been working on the following design

    1. Sort the names in ascending order of the number of incidences of each Name 2 since there are 35 of these as opposed to 10 Name1s.
    2. First Match round. Start with the Name2 that has the fewest number of mentions and match that with the available Name1s that has the highest number of occurences on the basis that there's more chance of matching a Name 2 if the available name1 matches hasn't already been used for another match.
    3. Next Match round Repeat step 2 using the next Name2 until all possible matches are complete.

    I've been experimenting with standard Excel functionality. Do you have Windows Excel 2019 or 365. This has useful UNIQUE() FILTER() and other new functions not available in prior versions. I'm toying with the idea of using a VBA macro, however your profile says you have Excel 2011 which sounds like it may be Excel for Mac not windows, would you confirm since personally I prefer not to use the Mac version of VBA.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Tweak Macro to find Matches instead of unique use dictionary
    By capson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2014, 03:06 PM
  2. [SOLVED] To find duplicates in a column and suffix them with numbers to make them unique
    By JishnuSurendran in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2014, 06:34 AM
  3. [SOLVED] Macro to find duplicates, concatenate Unique Values, then delete old duplicates
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2013, 04:32 PM
  4. Find unique matches for multiple criteria within a row of cells
    By Chase in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2012, 03:11 PM
  5. Find Matches in two different Spreadsheets without unique ID
    By oxy11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2011, 02:51 PM
  6. find matches in two columns of unique IDs and return result
    By fattony75 in forum Excel General
    Replies: 3
    Last Post: 10-27-2009, 02:59 PM
  7. howto find if there are matches in two columns of unique ids
    By fattony75 in forum Excel General
    Replies: 1
    Last Post: 10-27-2009, 07:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1