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

Question

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

How can I find how many unique sets of 3 (or 5 or 6 or 10 etc.) pairs are there?
How can I get these sets in separare columns?

Attached a sample file.

manolis · Answer

Hi MehdiJust received&nbsp; the below idea from an other Microsoft community member Andreas Killer which works.Many thanks again for assistance and attention.Kindest regards.&nbsp;-Split the data into 2 columns so that each cell contains one name, no headers above.- Open the VBA editor, insert a regular module and paste in the code below.- Create a button (form control) in the sheet and assign the Main macro from below to the button.- Select the cell inside the data which should appear as 1st pair in the 1st group.- Click the button.................................................Option ExplicitSub Main()&nbsp; Dim Groups As New Collection&nbsp; Dim Group As New Collection&nbsp; Dim Used As Object 'Scripting.Dictionary&nbsp; Dim Data, Pair&nbsp; Dim Count As Long&nbsp; Dim i As Long, j As Long, f As Long&nbsp;&nbsp; 'Number of pairs in a group&nbsp; Count = 5&nbsp; 'Create space for a pair&nbsp; ReDim Pair(1 To 2)&nbsp; 'For future use: If your pair has more then 2 people in a row use this instead:&nbsp; 'ReDim Pair(1 To UBound(Data, 2))&nbsp;&nbsp; 'Get the data and start position&nbsp; With ActiveCell&nbsp;&nbsp;&nbsp; Data = .CurrentRegion.Value&nbsp;&nbsp;&nbsp; f = .Row&nbsp; End With&nbsp; 'Be sure we have enough data&nbsp; If Not IsArray(Data) Then&nbsp;&nbsp;&nbsp; MsgBox "Select a cell inside the data an try again"&nbsp;&nbsp;&nbsp; Exit Sub&nbsp; End If&nbsp; If UBound(Data, 2) &lt; 2 Then&nbsp;&nbsp;&nbsp; MsgBox "Data must have at min. 2 columns"&nbsp;&nbsp;&nbsp; Exit Sub&nbsp; End If&nbsp;&nbsp; 'Prepare our variables&nbsp; Set Used = CreateObject("Scripting.Dictionary")&nbsp; Used.CompareMode = vbTextCompare&nbsp;&nbsp; 'Step 1: Analyse the data&nbsp;&nbsp; 'Initial position&nbsp; i = f&nbsp; Do&nbsp;&nbsp;&nbsp; 'Get a pair from this row&nbsp;&nbsp;&nbsp; For j = 1 To UBound(Pair)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Pair(j) = Data(i, j)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'One of them already used?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Used.Exists(Pair(j)) Then GoTo NextRow&nbsp;&nbsp;&nbsp; Next&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Add to current group&nbsp;&nbsp;&nbsp; Group.Add Pair&nbsp;&nbsp;&nbsp; For j = 1 To UBound(Pair)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Used.Add Pair(j), 0&nbsp;&nbsp;&nbsp; Next&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Full?&nbsp;&nbsp;&nbsp; If Group.Count = Count Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Store&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Groups.Add Group&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set Group = New Collection&nbsp;&nbsp;&nbsp; End IfNextRow:&nbsp;&nbsp;&nbsp; i = i + 1&nbsp;&nbsp;&nbsp; If i &gt; UBound(Data) Then i = 1&nbsp; Loop Until i = f&nbsp; 'Store last group if contain members&nbsp; If Group.Count &gt; 0 Then Groups.Add Group&nbsp;&nbsp; 'Step 2: Output&nbsp;&nbsp; 'Prepare&nbsp; ReDim Data(1 To Groups.Count * Count, 1 To UBound(Pair) + 1)&nbsp; 'Compile the data&nbsp; i = 0&nbsp; f = 0&nbsp; For Each Group In Groups&nbsp;&nbsp;&nbsp; f = f + 1 'Group number&nbsp;&nbsp;&nbsp; For Each Pair In Group&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i = i + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For j = 1 To UBound(Pair)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data(i, j) = Pair(j)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data(i, j) = f&nbsp;&nbsp;&nbsp; Next&nbsp; Next&nbsp;&nbsp; 'Create a new sheet and write the data into&nbsp; Dim Ws As Worksheet&nbsp; Set Ws = Sheets.Add(After:=ActiveSheet)&nbsp; With Ws&nbsp;&nbsp;&nbsp; With .Range("A1:C1")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Value = Array("Name A", "Name B", "Group")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Font.Bold = True&nbsp;&nbsp;&nbsp; End With&nbsp;&nbsp;&nbsp; With .Range("A2").Resize(UBound(Data), UBound(Data, 2))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Value = Data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .EntireColumn.AutoFit&nbsp;&nbsp;&nbsp; End With&nbsp; End WithEnd Sub

tanya denton · Answer

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 · Answer

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&nbsp; get these set/ groups of&nbsp; 3 (or 5 or&nbsp; 6....10 etc)&nbsp; 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 .&nbsp;Any idea will be appreciated.Again thank you!

mehdi hammadi · Answer

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.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'Build the different possible pairs from the preview columns, you have 03 possible combinasons&nbsp;regarding your data.- The first pair column '03 Pairs 01' is the concatanation of the columns 'Tech01', 'Tech02' and 'Tech03'- The&nbsp;second pair column '03 Pairs 02', is the concatanation of the columns 'Tech02', 'Tech03' and 'Tech04'- The&nbsp;third pair column '03 Pairs 03', is the concatanation of the columns 'Tech03', 'Tech04' and 'Tech05In a new sheet, copy all the resulting pairs in on column.Add a formula to count how many time&nbsp;each pair is duplicatedFilter the table to show only row with a count of duplicates greater than or equal 1Copy/past the result to a new locationRemove duplicates.See attachment for the solution.&nbsp;Regards,Mehdi

manolis · Answer

Hi Mehdi ,&nbsp;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.

Forum Discussion

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

6 Replies

Resources