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

Mvg Warzel

"Warzel" wrote in message
...
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
--

Mvg Warzel

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

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.


On Sat, 22 Jan 2005 20:57:33 +0100, Warzel
wrote:

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

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 wrote:
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.


On Sat, 22 Jan 2005 20:57:33 +0100, Warzel
wrote:


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




--
Het verschil tussen theorie en de praktijk is in theorie kleiner dan in
de praktijk

Mvg 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.

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.

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.

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

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 :

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.

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