Thread
:
excel duplicates on 2 worksheets
View Single Post
#
4
Posted to microsoft.public.excel.misc
Tom Ogilvy
Posts: n/a
excel duplicates on 2 worksheets
In an adjacent column in Sheet1 in row 1 put in this formula (or adjust for
row 2 if you have headers in row 1) [assumes phone numbers are in column D
on both sheets]
=countif(D1,Sheet2!D:D)0
then drag fill down the column. This
will produce a true in Sheet1 for a matching number in sheet2
Now in sheet 1, select this column and do Data=Filter=Autofilter
In the dropdown, select True
then select all the data and do Edit=Delete
only the visible cells will be deleted
Take out the Filter by doing Data=Filter=Autofilter
Test this on a copy of your workbook.
--
Regards,
Tom Ogilvy
"Luke Rogers" <Luke
wrote in message
...
The situation: I have two worksheets both of which have columns named:
1) Name
2: Address
3) Post Code
4) Telephone Number
I am using the telephone number as the unique identifier as we only have
to
store data 'per household' - not 'per customer'.
On the first worksheet there is a list of all the customers.
On the second worksheet there is a list of customers who wish to be taken
off our customer list.
I need to use the second worksheet to delete the customer entries in the
first worksheet.
I have tried to use the worksheets together to identify duplicates but
there
seems to be a problem doing this between two worksheets.
Can anyone tell me a simp way of putting this equation into excel:
if sheet2 single telephone number = any sheet 1 telephone number
then delete customer record from sheet1.
Thanks guys!
Reply With Quote