Forum Discussion

Manolis's avatar
Manolis
Copper Contributor
Nov 08, 2016

How to get sets of unique data from a column which contains combined data (names)

We have a list with combined pairs of technicians names, as demonstrated in the screenshot below.

  1. How can I find how many unique sets of 3 (or 5 or 6 or 10 etc.) pairs are there?

  2. How can I get these sets in separare columns?

Attached a sample file.

6 Replies

  • Mehdi HAMMADI's avatar
    Mehdi HAMMADI
    Brass Contributor

    Hi Manolis,

    If I correctly understoud your request, I propose you this method to retrive the sets of 3 pairs using the data you provided.

    1. Use the "Text to column" feature to extract each technician in a separated column. in you case you will have 06 columns 'Tech01' to 'Tech06'
    2. Build the different possible pairs from the preview columns, you have 03 possible combinasons regarding your data.
      - The first pair column '03 Pairs 01' is the concatanation of the columns 'Tech01', 'Tech02' and 'Tech03'
      - The second pair column '03 Pairs 02', is the concatanation of the columns 'Tech02', 'Tech03' and 'Tech04'
      - The third pair column '03 Pairs 03', is the concatanation of the columns 'Tech03', 'Tech04' and 'Tech05
    3. In a new sheet, copy all the resulting pairs in on column.
    4. Add a formula to count how many time each pair is duplicated
    5. Filter the table to show only row with a count of duplicates greater than or equal 1
    6. Copy/past the result to a new location
    7. Remove duplicates.

    See attachment for the solution.

     

    Regards,

    Mehdi

    • Manolis's avatar
      Manolis
      Copper Contributor

      Hi Mehdi ,

       

      Thank you fo your effort.

      Sorry that im not enough clear (my english.....)

      In each cell there are 2 tecnicians ie A2 contains Mr Andreou Dimitrios and Mr Apostolou Vasileios.

      Okay in bulding pairs sheet we can have two columns Tech01 and Tech 02. In total we have 1266 unique pairs.

      We want to divide this summation in the groups (3 or 5 or 10 etc) of pairs.

      The rule is that each group must contain always unique names.

      • Mehdi HAMMADI's avatar
        Mehdi HAMMADI
        Brass Contributor

        Hi Manolis,
        My english is also not so good. So what I propose you is to provide a exemple with original data and the final result you want to obtain not the whole of the data but just a sample.

  • Tanya Denton's avatar
    Tanya Denton
    Iron Contributor
    1. Highlight column, go to Data tab, 'Text to Columns', choose Delimited then click Space 2. Then highlight all columns, go to Data tab and 'Remove Duplicates'
    • Manolis's avatar
      Manolis
      Copper Contributor

      Hi Tanya ,

      Many thanks for the swift response!

      Please note that each cell already contain a unique pair of technicians.

      We are looking for a way to find how may sets of 3 pairs (or 5 or 6 or 10..) there are in the column and how we can  get these set/ groups of  3 (or 5 or  6....10 etc)  unique pairs in separate columns.

      i.e if someone name -first left word in the cell- is in the group cannot be into next group .

       

      Any idea will be appreciated.

      Again thank you!

Resources