Extract (not delete) double entries of two columns combined

[Hermann3]

Hello again,

I'll try to explain what I want:
I have a list of participants of trainings.
Column F has the last names
Column G has the first names

Now I have many trainings and some participants attend more than one training. So, I want to "extract" the first and last names of people to a separate sheet without getting the duplicates (where first AND last name is the same). All this so I can make a gender analysis that's not skewed.

Any ideas?

Hermann

 

[Marcelo Branco]

If you are using Excel 2007 or higher

Copy (Ctrl+C) the data in columns F and G
Paste(Ctrl+V) on a new sheet
Select the data on the new sheet and Data > Remove duplicates

M.

 

[Hermann3]

Thanks Marcelo,

I would like to have this automatically, i.e. as soon as I fill in new entries for a training, I want them automatically copied to the other sheet, however, without duplicates. Possible?

 

[Hermann3]

Here's why this doesn't work.
If I take out only the two columns, I can't make a Pivot Table according to sex.
If I keep the column with sex (M,F), then deleting duplicates doesn't have an effect on my pivot table. The problem is that excel deletes the duplicates, but only in the two columns. It would have to delete the entire row, though.

I can't mark the entire row, however, because that would eliminate ALL duplicates (and, of course, there are many, because participants are either male or female).

 

[Hermann3]

The other problem is that excels removes duplicates independently, i.e. if I have the following list
Miller ------- John
Jackson ------ John
Miller ------- Daniel
Smith -------- Sarah
Jackson ------ Sarah
Miller --------- John

it will actually delete all entries, while there is only one duplicate (John Miller).

 

[Marcelo Branco]

Could you post a data sample, say 10 rows including headers, and expected results?

M.

 

[Marcelo Branco]

I made a test using your data sample in #5 , selecting both columns, Excel has deleted just one row.
Miller --- John

M.

 

[Hermann3]

<table width="349" border="0" cellpadding="0" cellspacing="0"><col style="width: 71pt;" width="95"> <col style="width: 75pt;" width="100"> <col style="width: 68pt;" width="90"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 71pt;" width="95" height="20">Training ID</td> <td style="width: 75pt;" width="100">Last Name</td> <td style="width: 68pt;" width="90">First Name</td> <td style="width: 48pt;" width="64">Sex</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_A</td> <td>Miller</td> <td>Johnny</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_A</td> <td>Bayron</td> <td>Josephine
</td> <td>F</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_A</td> <td>Jackson</td> <td>Reynaldo</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_A</td> <td>Fuentebella</td> <td>Eduardo G</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_A</td> <td>Pebbles</td> <td>Rosie P</td> <td>F</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_B</td> <td>Smith</td> <td>Jack</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_B</td> <td>Manning</td> <td>Robert</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_B</td> <td>Pebbles
</td> <td>Rosie P
</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_B</td> <td>Shaw</td> <td>Sonia</td> <td>F</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_C</td> <td>Jackson</td> <td>Lea B</td> <td>F</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_C</td> <td>Escobar</td> <td>Henry</td> <td>M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_C</td> <td>Miller</td> <td>Natalia M</td> <td>F</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RBM_C</td> <td>Samson</td> <td>Josephine</td> <td>F</td> </tr> </tbody></table>
This would be the sample data. I want excel to clear all rows that have duplicates names of participants (in this case only row 8 with Rosie P Pebbles, but not Natalia M Miller nor Josephine Samson).

Obrigado.

 

[Hermann3]

Any help with this?