Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Luke Rogers
 
Posts: n/a
Default excel duplicates on 2 worksheets

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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default excel duplicates on 2 worksheets

I'd add an extra column on the worksheet that should be cleaned up--not the
worksheet with all the phone numbes to delete.

=isnumber(match(d2,sheet2!a:a,0))

If there's a match, then you'll get True. No match = False.

Then apply data|Filter|autofilter and show the True's. Delete the visible rows
and then delete the helper column.

(Or sort all 5 columns (A:E) by column E and delete that group of True's.)

Luke Rogers wrote:

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!


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default excel duplicates on 2 worksheets

Functions & equations don't, by themselves, perform actions (such as delete).
They return results. The steps I'd take:
On sheet 1, in cell E2 enter the formula
=if(isna(match(d2,Sheet2!D:D,false)),"","delete!") .
Copy that down (using autofill) to each row on sheet1.
Turn on autofilter (click one cell in the table then Data Filter
Autofilter)
Use the drop-down in E1 to select 'delete'.
Click in the first cell in column containing the word 'delete' and
ctrl+shift+down arrow to select all such cells.
Right-click and select DeleteRow.
--Bruce

"Luke Rogers" wrote:

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!

  #4   Report Post  
Posted to microsoft.public.excel.misc
Tom Ogilvy
 
Posts: n/a
Default 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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search multiple worksheets - Excel 97 Fybo Excel Discussion (Misc queries) 3 September 16th 05 04:19 PM
how to use input formula from several worksheets in excel? Mona Excel Worksheet Functions 1 September 16th 05 01:03 AM
Appending excel worksheets Rbuzard Excel Worksheet Functions 3 September 13th 05 11:57 AM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Want Excel to break links between worksheets in same file eclectic_kx Excel Worksheet Functions 1 May 25th 05 06:38 PM


All times are GMT +1. The time now is 10:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"