Comparing Data?

  • Thread starter Thread starter Ket.Kana
  • Start date Start date
K

Ket.Kana

Hello,

I am using Office 2K with Windows 2K. I have a spreadsheet that is used as
a mailing list. In column A I have company names, in column B I have
contacts.
I need to search and delete duplicate rows. What I need to search for is
a contact name appearing more than once against the company.
The spreadsheet looks like this.

A B
Company name FullName
ABC Ltd Joe Bloggs
ABC Ltd Jane Bloggs
ABC Ltd Fred Bloggs
ABC Ltd Joe Bloggs

In this scenario, I would need to delete the second occurrance of Joe
Bloggs to prevent a mail being sent twice. Any help greatly appreciated.

TIA

Ket
 
Hello,

I am using Office 2K with Windows 2K. I have a spreadsheet that is used as
a mailing list. In column A I have company names, in column B I have
contacts.
I need to search and delete duplicate rows. What I need to search for is
a contact name appearing more than once against the company.
The spreadsheet looks like this.

A B
Company name FullName
ABC Ltd Joe Bloggs
ABC Ltd Jane Bloggs
ABC Ltd Fred Bloggs
ABC Ltd Joe Bloggs

In this scenario, I would need to delete the second occurrance of Joe
Bloggs to prevent a mail being sent twice. Any help greatly appreciated.

TIA

Ket
Click to expand...

One way is to use
Data > Filter > Advanced filter
with Unique records ticked.
This will hide any rows containing duplicate records.

You can then select what you see and use
Edit > Go To > Special > Visible cells only
At this point you have only unique records selected, so you can copy and
paste into a new list of unique records.
 
Kit:

Another way that I've used is to use the EXACT function.
There are a few more steps involved:

1. Sort the list by name.
2. In cell "C3" enter the formula, "=EXACT(B3,B2)" This
compares the text in B2 and B3. If they are exactly the
same, it will return "TRUE"; otherwise, FALSE. The
result will look like:

A B C
Company FullName Exact?
ABC Ltd Fred Bloggs
ABC Ltd Jane Bloggs FALSE
ABC Ltd Joe Bloggs FALSE
ABC Ltd Joe Bloggs TRUE


3. Select the entire column "C" ; Copy the cells and then
use Paste Special (Values) from the Edit menu to
overwrite the formulae with the values.
4. Sort the list by column "C"
5. Delete all the rows with "TRUE"

Note that it is possible that there will be individuals
that have the same name at different companies. It might
be better to compare e-mail addresses instead (I've been
through this before ...)

Good luck,
Mike.

(e-mail address removed)
 
Back
Top