Removing duplicates testing in 2 coloms

[Warzel]

I want to remove duplicates from a sheet testing for values in two coloms

in the first colom are hunderds of firstnames
in the second colom hunderds of lastnames

What i want is to remove duplicates when both first and last name in a
row are equal

 

[Fredrik Wahlgren]

I want to remove duplicates from a sheet testing for values in two coloms

in the first colom are hunderds of firstnames
in the second colom hunderds of lastnames

What i want is to remove duplicates when both first and last name in a
row are equal

There's an easy way to do this manually. Assuming that first name is in
column A and last name in column B

1) In column C, enter =CONCATENATE(TRIM(A1),TRIM(B1)). Drag this formula
down as many rows as needed
2) Select columns A to C
3) Select Data|Sort. Sort on column C
4) In D1, enter =IF(D1=D2,True,False). Data is now sorted, that's why this
comparison makes sense
5) Select column D and press CTRL C
6) Select Paste|Special and in the dialog, click Values. Paste the values
into column D
7) Select columns A to D
8) Select Data|Sort. Sort on column D

You need to pay special attention to the last row when you do step 4.
Manually enter true or false. Having done this, all your duplicates will
appear last. Now, it should be easy to remove the duplicates.

/ Fredrik

 

[Myrna Larson]

If there is data ONLY on those two columns, you can use Data/Filter/Advanced
filter and select the Unique Records option. After you've applied the filter,
copy the visible rows to another location.

If there is other data besides the names, you can use a couple of "helper"
columns. Let's say last names are in A, first names in B. Insert two new
columns, C and D. In C1 put this formula and copy it down

=A1&" "&B1

In D1 put this formula:

=IF(COUNTIF($C$1:$C1,C1)=1,"X","")

and copy it down.

Then use Data/AutoFilter to display only the rows with an X in column D. Again
copy the visible rows to another location. Then you can delete the new columns
you inserted.

 

[Warzel]

That is not working, it only shows the names that only apear once in the
list. If a name has more then one entries it is not displayed at all.
further more is it not possible to do this in VBA.

Thanks anyway

 

[Myrna Larson]

That is not working, it only shows the names that only apear once in the

list. If a name has more then one entries it is not displayed at all.
further more is it not possible to do this in VBA.

That isn't true. Sounds like you didn't type the formula correctly.

It will show an X for the *first occurrence* of a name in the list. The 2nd or
3rd occurrence of the same name will look blank. In other words, if you
extract only the rows with an X, you will have all of the unique names -- same
result as the Advanced Filter/Unique Records only.

Yes, of course you can do this in VBA, either by coding the steps I described
or, more efficiently, by using Advanced Filter.

You can also use "brute force" method: reading the two columns into a VBA
array, concatenating the first and last names into a 2nd array, then comparing
each entry to those above it in the list (I'd use MATCH) and copying the 1st
occurrence to yet a 3rd array.

 

[Warzel]

That is not working, it only shows the names that only apear once in the

That isn't true. Sounds like you didn't type the formula correctly.

It will show an X for the *first occurrence* of a name in the list. The 2nd or
3rd occurrence of the same name will look blank. In other words, if you
extract only the rows with an X, you will have all of the unique names -- same
result as the Advanced Filter/Unique Records only.

Yes, of course you can do this in VBA, either by coding the steps I described
or, more efficiently, by using Advanced Filter.

You can also use "brute force" method: reading the two columns into a VBA
array, concatenating the first and last names into a 2nd array, then comparing
each entry to those above it in the list (I'd use MATCH) and copying the 1st
occurrence to yet a 3rd array.

Oke as i'am dutch i have a dutch version of excel, copying and pasting
of your example (like many examples here) give an error message.

The formula in dutch is
=ALS(AANTAL.ALS(A:A;A1)=1;"x";"")

The IF statement is know as ALS
COUNTIF i think is AANTAL.ALS

notice the ; instead of the , using , wil create an error here

 

[keepITcool]

Warzel,

d/l my translateIT addin.
that allows for instant translation of pasted formulas
from and to Dutch (and many other languages)

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Warzel wrote :