Forum Discussion
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.
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.
6 Replies
- Mehdi HAMMADIBrass 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.
- 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 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 - In a new sheet, copy all the resulting pairs in on column.
- Add a formula to count how many time each pair is duplicated
- Filter the table to show only row with a count of duplicates greater than or equal 1
- Copy/past the result to a new location
- Remove duplicates.
See attachment for the solution.
Regards,
Mehdi
- ManolisCopper 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 HAMMADIBrass 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 DentonIron Contributor1. 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'
- ManolisCopper 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!