Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet question
I have two spreadsheets. On one I have 20,000 customers with customer ids
which are in numeric form like 19678. On the other spreadsheet I have 500 customers with customer ids. The second speadsheet is a list of customers I need to delete from the larger 20,000 customer list. All of the customer ids on the small list exist on the larger list. I could search for each id that I want to delete individually but that would be very time consumming. Is there a easy way to tell excel to look at the smaller list and whenever it sees a match in the bigger list the match is deleted? Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet question
I will assume the IDs are column A in both sheets and that row 1 is either
empty or contains labels. If not, insert a bank row 1 On the large sheet, insert a new column A In A2 of the large sheet enter =IF(ISERROR(VLOOKUP(B2,Sheet2!$A$2$A$501,1,FALSE)) ,0,1) Copy down the column - easy way is to double click the fill handle (little solid square in lower right corner of active cell) Now you have 0's and 1's where 1 means the same ID is in both sheets Select all of the data INCLUDING the top row and use Date | Filter | Auto Filter IN the drop down box in A1 chose 1 Now only the duplicate records are visible Select the row number for these and do a Rows Delete Turn off the filter; delete column A in the large sheet. Done best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JFSJ" wrote in message ... I have two spreadsheets. On one I have 20,000 customers with customer ids which are in numeric form like 19678. On the other spreadsheet I have 500 customers with customer ids. The second speadsheet is a list of customers I need to delete from the larger 20,000 customer list. All of the customer ids on the small list exist on the larger list. I could search for each id that I want to delete individually but that would be very time consumming. Is there a easy way to tell excel to look at the smaller list and whenever it sees a match in the bigger list the match is deleted? Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet question
"Bernard Liengme" wrote: I will assume the IDs are column A in both sheets and that row 1 is either empty or contains labels. If not, insert a bank row 1 On the large sheet, insert a new column A In A2 of the large sheet enter =IF(ISERROR(VLOOKUP(B2,Sheet2!$A$2$A$501,1,FALSE)) ,0,1) Copy down the column - easy way is to double click the fill handle (little solid square in lower right corner of active cell) Now you have 0's and 1's where 1 means the same ID is in both sheets Select all of the data INCLUDING the top row and use Date | Filter | Auto Filter IN the drop down box in A1 chose 1 Now only the duplicate records are visible Select the row number for these and do a Rows Delete Turn off the filter; delete column A in the large sheet. Done best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JFSJ" wrote in message ... I have two spreadsheets. On one I have 20,000 customers with customer ids which are in numeric form like 19678. On the other spreadsheet I have 500 customers with customer ids. The second speadsheet is a list of customers I need to delete from the larger 20,000 customer list. All of the customer ids on the small list exist on the larger list. I could search for each id that I want to delete individually but that would be very time consumming. Is there a easy way to tell excel to look at the smaller list and whenever it sees a match in the bigger list the match is deleted? Thanks in advance for your help. Thank You! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet question
Glad it worked for you.
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JFSJ" wrote in message ... "Bernard Liengme" wrote: I will assume the IDs are column A in both sheets and that row 1 is either empty or contains labels. If not, insert a bank row 1 On the large sheet, insert a new column A In A2 of the large sheet enter =IF(ISERROR(VLOOKUP(B2,Sheet2!$A$2$A$501,1,FALSE)) ,0,1) Copy down the column - easy way is to double click the fill handle (little solid square in lower right corner of active cell) Now you have 0's and 1's where 1 means the same ID is in both sheets Select all of the data INCLUDING the top row and use Date | Filter | Auto Filter IN the drop down box in A1 chose 1 Now only the duplicate records are visible Select the row number for these and do a Rows Delete Turn off the filter; delete column A in the large sheet. Done best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JFSJ" wrote in message ... I have two spreadsheets. On one I have 20,000 customers with customer ids which are in numeric form like 19678. On the other spreadsheet I have 500 customers with customer ids. The second speadsheet is a list of customers I need to delete from the larger 20,000 customer list. All of the customer ids on the small list exist on the larger list. I could search for each id that I want to delete individually but that would be very time consumming. Is there a easy way to tell excel to look at the smaller list and whenever it sees a match in the bigger list the match is deleted? Thanks in advance for your help. Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a 2-3 page Excel spreadsheet as an object in MS Wo | Excel Discussion (Misc queries) | |||
Loading spreadsheet into Excel 2000, Excel is blank with no worksh | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel 2K3: Opening a spreadsheet, also opens book1.xls? | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |