Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search multiple worksheets - Excel 97 | Excel Discussion (Misc queries) | |||
how to use input formula from several worksheets in excel? | Excel Worksheet Functions | |||
Appending excel worksheets | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Want Excel to break links between worksheets in same file | Excel Worksheet Functions |