Sorting out repeat combinations of names

%3CLINGO-SUB%20id%3D%22lingo-sub-1862812%22%20slang%3D%22en-US%22%3ESorting%20out%20repeat%20combinations%20of%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1862812%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20trouble%20with%20a%20good%20way%20to%20filter%20out%20a%20repeat%20set%20of%20names%20in%20a%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%20I%20have%20ten%20Projects%2C%20I%20have%20randomly%20selected%20team%20members%20to%20work%20on%20each%20project%2C%20however%20I%20don't%20want%20the%20same%20combination%20of%20team%20members%20to%20work%20on%20any%20given%20project.%20(In%20my%20example%20below%20Project%201%20and%208%20are%20the%20same%20team%20members)%20can%20someone%20help%20me%20with%20a%20good%20way%20to%20flag%20the%20repeat%20combination%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JOle87_0-1604699304128.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232032i0ED1863774DA8BF9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JOle87_0-1604699304128.png%22%20alt%3D%22JOle87_0-1604699304128.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1862812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864093%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20repeat%20combinations%20of%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354614%22%20target%3D%22_blank%22%3E%40JOle87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20truth%20is%2C%20it%20would%20be%20a%20lot%20easier%20to%20help%20you%20with%20a%20precise%20example%20if%20you%20posted%20the%20spreadsheet%20you%20used%20to%20create%20that%20image.%20Otherwise%20you're%20asking%20anybody%20here%20to%20spend%20time%20re-creating%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20here%20are%20the%20steps%20I'd%20go%20through%20if%20I%20had%20the%20spreadsheet.%20These%20steps%20do%20presume%20you%20have%20the%20most%20recent%20version%20of%20Excel%2C%20which%20contains%20what%20are%20called%20Dynamic%20Array%20functions%20like%20SORT%2C%20UNIQUE%2C%20FILTER.%20I'm%20%3CU%3E%3CEM%3Ealso%3C%2FEM%3E%3C%2FU%3E%20presuming%20that%20each%20team%20always%20has%20the%20same%20number%20of%20members.%20If%20sometimes%20there%20are%20teams%20of%208%2C%20sometimes%205%2C%20etc.%2C%20it%20gets%20trickier%2C%20but%20still%20can%20be%20solved.%20Write%20back%20if%20that's%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20do%20have%20that%20most%20recent%20version%20of%20Excel%3C%2FP%3E%3CUL%3E%3CLI%3EUse%20SORT%20to%20arrange%20the%20names%20of%20each%20time%20in%20alphabetical%20order%3C%2FLI%3E%3CLI%3EThen%20write%20a%20formula%20that%20compares%20each%20name%2C%20in%20sequence%2C%20with%20the%20name%20in%20the%20same%20spot%20in%20every%20other%20team%20and%20gives%20a%20zero%20if%20there's%20a%20match%2C%20a%201%20if%20there%20is%20not.%3C%2FLI%3E%3CLI%3ECap%20it%20off%20with%20a%20formula%20that%20sums%20all%20of%20those.%3C%2FLI%3E%3CLI%3ESo%20long%20as%20the%20grand%20total%20is%20greater%20than%20zero%20the%20team%20is%20unique.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am having trouble with a good way to filter out a repeat set of names in a list.

 

For example: I have ten Projects, I have randomly selected team members to work on each project, however I don't want the same combination of team members to work on any given project. (In my example below Project 1 and 8 are the same team members) can someone help me with a good way to flag the repeat combination?

 

JOle87_0-1604699304128.png

 

1 Reply
Highlighted

@JOle87 

 

The truth is, it would be a lot easier to help you with a precise example if you posted the spreadsheet you used to create that image. Otherwise you're asking anybody here to spend time re-creating it.

 

That said, here are the steps I'd go through if I had the spreadsheet. These steps do presume you have the most recent version of Excel, which contains what are called Dynamic Array functions like SORT, UNIQUE, FILTER. I'm also presuming that each team always has the same number of members. If sometimes there are teams of 8, sometimes 5, etc., it gets trickier, but still can be solved. Write back if that's the case.

 

If you do have that most recent version of Excel

  • Use SORT to arrange the names of each time in alphabetical order
  • Then write a formula that compares each name, in sequence, with the name in the same spot in every other team and gives a zero if there's a match, a 1 if there is not.
  • Cap it off with a formula that sums all of those.
  • So long as the grand total is greater than zero the team is unique.