comparing values on different sheets and deleting

Is there a way delete value on sheet2 from the values found on sheet1?  For 
example I have a list of items on sheet1.  I need to delete all values from 
sheet1 that match on sheet2.  thank. np
0
Utf
12/10/2009 4:46:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
451 Views

Similar Articles

[PageSpeed] 22

You might have to do this with a few steps.

Add a column in list #1 such as:

=VLOOKUP(A2,Sheet2!$A$1:$A$5,1,FALSE)

Assuming A2 is the item you are looking for…
Assuming the names that might have dups in them is A1:A5 on Sheet2

Anything *not* found will be listed as N/A which mean Not Available.

Step 2, turn on Auto Filter.  Data Filter Autofilter in 2003 or Home Tab 
Sort and Filter, Filter.

Step 3 uncheck #N/A and you will be left with only duplicate items.

Step 4 Delete those rows.

Hope that helps.

Frank


"np" wrote:

> Is there a way delete value on sheet2 from the values found on sheet1?  For 
> example I have a list of items on sheet1.  I need to delete all values from 
> sheet1 that match on sheet2.  thank. np
0
Utf
12/10/2009 4:56:01 PM
Reply:

Similar Artilces:

can I delete an email through system manager or other utility
I support a large network and occasionally spam will get through and cause problems with my veritas backup. I know which email caused the problem and who's mailbox it lives in. Is there a way I can delete from the server or with another utility without going through the users outlook. you can exmerge it out of the mailbox if you have the exact subject line...but I do not see how spam getting through could cause problems with your backups... "darren" <darren@yahoo.com> wrote in message news:%23jZ6hS%23nEHA.3908@TK2MSFTNGP09.phx.gbl... > I support a large network and ...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

summary sheet?
I am using Excel 2K and Windows 2K. I have a set of worksheets in a workbook that are identically formatted. Is it possible to create a summary sheet that will automatically place the worksheet name in one column and information next to it from say cells A1,C1,F30 from all worksheets. I'm not a great VB whizz. Any help greatly appreciated. TIA Ket This will add a new sheet and create the list. Press ALT+F11, go to Insert > Module, and paste in the code. Then run the macro: Sub Summary() Dim ws As Worksheet Dim i As Integer i = 2 Sheets(1).Activate Sheets.Add With Sheets(1) ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

join columns, keep both values
I have a multicolumn database with text entries. I want to join the cells in Col. B with the cells in Col.C and keep all the text. For instance, B3 may be "1N", C3 may be "4W". I want the combined column to read "1N 4W". How do I do this?!? TNX. -- FourPenny ------------------------------------------------------------------------ FourPenny's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23864 View this thread: http://www.excelforum.com/showthread.php?threadid=375062 Try this: =B3&" "&C3 -->"1N 4W&q...

Pass value to combo box
I have an unbound text box (txtSaveVal), an unbound combo box (cboName), and a string variable strSaveVal. me.txtSaveVal=strSaveVal .............. Works fine me.cboName=strSaveVal ............... Does Not Work I simply want to put the value of strSaveVal into the combo box, as though I had typed it in, BEFORE pressing the enter key. Why isn't this working? esee, Why are you doing this? A combo box is not intended to accept a value from another control, but provide a means to select a value from a list of values either from a table, a query, or a manually ...

Compare Monthly Item Sales
I am trying to create a report that will allow me to compare a specific item or items by suppliers from one month to another month. (Example: All items sold by ABC in March 2007 Vs. March 2006 or ABC items sold in March 2007 Vs. Feb 2007) I see that there are sales reports that compare sales totals between months, but I have had no luck trying to convert that information to actual items or companies. Again looking for QTY totals not sales totals. Hope someone has some neat ideas. Thanks, Neil HI you got me. well good news is that a month later i have develop this report for Mr. David ...

Exchange Server 2003: Can I set a public folder or information store up on different server?
Hi I was wondering if it is possible to set up a second information store for public folders onto a separate server. The primary information store would reside on server 1 with its private and public edb file but i would like a second public accessible edb file set up on a second server distinct from the exchange server. Thanks in advance for any help. Marco You mean a Public Folder server? Yes, you can. If your PF traffic/performance requirements justify it, as they do in some organizations, you can setup a setup a separate server and host Public Folders on it. -- Bharat Suneja MVP -...

3M SandBlaster 423-120 Flexible Sanding Pad, 3.7 x 9.25-Inch, 120-Grit, Rectangle, 2-Sheet
Price:$7.30 Image: http://discountadvisors.info/image.php?id=B000GLHW3Q Best deal: http://discountadvisors.info/index.php?id=B000GLHW3Q Sandblaster, 2 Pack, 120G 3.7" x 9" Large Flexible Abrasives Detailed Sandpaper, Lasts 10 Times Longer Than Normal Sandpaper, Extreme Durability, Ultimate Comfort, Sandpaper Can Also Be Used On Detailed Flexible Abrasives Tool True Value #630-485. SIMILAR PRODUCTS: 3M SandBlaster 423-180 Flexible Sanding Pad, 3.7 x 9.25-Inch, 180-Grit, Rectangle, 2-Sheet:http://discountadvisors.info/index.php?id=B000GLE1OO 3M SandBlaster 413-220 Flexib...

Value list
i need help with making value lists in excel so that i can make my data base consistant with the data i am entering That's not much to go on. What is a value list? And what constitutes making your database consistent with the data you are entering? Post back and explain in detail what you have, what you want to do, and what you want to happen. Some examples are always good. HTH Otto "Maint Girl" <Maint Girl@discussions.microsoft.com> wrote in message news:4EDE3701-B704-4BE8-B449-58662FDCB495@microsoft.com... > i need help with making value lists in ...

different behavior in Excel2002
I have an empty WB with VBA code in IT in Excel 2002: Sub main() Dim wb As Workbook Set wb = ActiveWorkbook 'wb.SaveAs Filename:="\\nygisw906107\My Documents\test.xls" wb.SaveAs Filename:="c:\Documents and Settings\1igodzin\My Documents\test.xls" 'wb.Save End Sub I am trying to save this WB using wb.Saveas in the same place with the same name. Depending how I Open the WB only one of these lines is working. 1. open using File|Open . in this case second statement is working. 2. open using Start|Run \\nygisw906107\My Documents\test.xls just first statement is workin...

Please add delete action to IMF for information store
Can *anyone* in the Microsoft Exchange team respond to this: Please can the option to *delete* messages be added to the IMF actions for an information store? Why would we want to do this? We are a K12 school and have two Information stores, one for staff, one for students. It is quite acceptable to deliver spam into the junk email folder for staff. It is *not* acceptable to do this for students. Even though the UCE level is getting set correctly (ie the message is correctly classified as spam) the inability to *delete* such messages from the student information store results in adult m...

contacts appear as deleted in my list when they didn't delete me
I have this situation come up every once in a while is anyone else experiencing this? and what is causing the miss labeling? -- Four Generations Of Trust And Betrayal...One Legacy Skywolfe Hi Skywolfe, Just to confirm, how are you identifying them as being deleted? -- Jonathan Kay Microsoft MVP - Windows Live Messenger MSN Messenger/Windows Messenger MessengerGeek Blog: http://www.messengergeek.com Messenger Resources: http://messenger.jonathankay.com (c) 2009 Jonathan Kay - If redistributing, you must include this signature or citation -- "Rage Skywolfe"...

Unable to delete a task request from inbox in outlook
Hi all, Does anyone have an idea how I delete a task request from my inbox. When I try to delete the task request i get the outlook dialog box up, which states. "The task folder could not be found. Could not open the item. Try again." Any help with this would be appreciated. Thanks in advance. ...

How to delete here and there in a worksheet....
Hi... How can I delete the contains of all unlocked cells in a worksheet in one sweep? I have this worksheet with 6 * 13 * 33 cells scattered around the entire spreadsheet, divided by an unknown number of hidden and locked cells containing formulas vital to the entire spreadsheet. I have hidden the locked cells. I have removed the mark letting me choose locked cells. I cannot mark the whole spreadsheet in order to hit delete. -- Regards Zadig Galbaras (nick) ----- Sub UnLocked_Cells() Dim cell As Range, tempR As Range, rangeToCheck As Range For Each cell In ActiveSheet.Used...

How do I remove the outline protection in Excel sheet?
When I use the sheet protection feature of Excel, I can not unprotect the outlining feature of the spreadsheet. I would like to protect some cells in the spreadsheet, but not disable the outlining expanding and collapsing feature. I am not asking to edit the outline. I just want to use the outlining features as a means to navigate large sheets. Currently, I have to unprotect the sheet in order to expand or collapse the outline. Is there a way to view a protected sheet with the outlining features enables? If you already have the outline applied, you can protect the worksheet in code...

Excel macro to insert rows if a cell does not equal the value above that cell
I have a spreadsheet listing columns of information for many different people. If more than one row exists for the same person, I'd like to insert 2 rows after the row so that the group is separated from the next group. For example, here's how the data appears now: LastName Account# Balance Martin 1 500.00 Martin 2 750.00 Smith 5 100.00 Thomas 9 900.00 Here's what I'd like it to look like after running the macro: LastName Account# Balance Martin 1 500.00 Martin 2 75...

How to delete the note pad image?
I deleted the text in the note and the image of the note pad is still visible. How can I delete it? This is a multi-part message in MIME format. ------=_NextPart_000_0024_01CACBA0.A45BB580 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi Timmy, Welcome to this Microsoft Project newsgroup :) Are you referring to the Notes tab in the Task Information dialog or in = the Notes column? If so, then deleting all the text should prevent the = Notes indicator appearing in the Indicator column. Make absolutely sure = ther...

How can I find automatic links in an Excel-sheet?
I have an Excel-sheet with "automatic links" to other workbooks. At least according to Excel, so every time when I open the document I have to click 'No' to the questions whether I want to update these. The problem is I can not find this/these automatic link/s. Is there a way to find all automatic links so that I can delete them? Tobias wrote: > I have an Excel-sheet with "automatic links" to other workbooks. At > least according to Excel, so every time when I open the document I > have to click 'No' to the questions whether I want to update th...

cannot delete custom page in Business Portal
I created a page and I want it deleted. When I try to remove the page, I get the error - this item is currently being used in a menu or task and cannot be removed. I have not included this page anywhere. How can I figure out what is stopping the page from being deleted? Go to Manage Navigation and delete the menu first then go to Manage Portal Pages then Remove it -- http://ddelprado.blogspot.com "Jack Tundra" wrote: > I created a page and I want it deleted. When I try to remove the page, I get > the error - this item is currently being used in a menu or task and ...

Cannot delete mail in deleted items
We migrated all of our mailboxes to a new server this weekend. We are having a strange problem today. Nobody can delete anything in there deleted items. It doesn’t give them an error. It just doesn’t delete the item. I’ve also noticed that the “recover deleted items” is grayed out. shift delete will work, but I want to be able to use delete. The new server is running Exchange 2003 sp2. The clients are running Office XP SP3. any ideas? what happens if you right-click on the deleted items folder and select, "Empty deleted items"? -- Susan Conkey [MVP] "jbwerner&...

Delete Timesheet Period that contains actual hours ?
when i try to delete the timesheets periods. some timesheet periods are unable to delete and delete button showing disable. I want to delete all timesheets periods whether they have some actual hours and some have not. how to this . kindly reply me soon Hi Sam, make sure that your periods are not closed. Go to 'Delete Enterprise Objects', select Timesheets and the appropriate period and delete them. This will delete timesheets even with actuals. After deleting them, you can delete periods. Regards Barbara "Sam" <Sam@discussions.microsoft.com&g...

delete saved files
Is there a way of deleting the previously saved Excel files? It is maddening to see all those things that I can no longer open. -- Dawn Tools > Options > GeneralTab > Uncheck the Recently used files box Vaya con Dios, Chuck, CABGx3 "herd" <herd@discussions.microsoft.com> wrote in message news:089179B0-5516-4934-ACE9-9D664BF5672E@microsoft.com... > Is there a way of deleting the previously saved Excel files? It is maddening > to see all those things that I can no longer open. > -- > Dawn In office 2007 I have headings of Home, Insert, Page Layout, F...

Counting Three Different Words In One Column
H ithere, Can someone please help me? I have a column which can only have option (due to validation), these are:- New Overdue Updated Closed I would like to count the number of times each one has been entered. Each count would be in a different cell on a different worksheet. have tried searching this forum and found answers similar to what i' looking for but i could not get any of them to work for some reason? Thanks very much for your time. Swmasso -- Message posted from http://www.ExcelForum.com Hi you need to use the countif function for this so if your range is ...

Delete a specific folder from all users' mailboxes
Exchange 2003 SP2 We have a legacy folder hanging around that McAfee created in each users' mailbox. I want to delete the folder from everyone's mailbox. Is there a way to do that without going into each individual mailbox? Thanks for your help! Steven Shock The only way I know of to do this would be to write a script that opens each mailbox and deletes the folder if it exists. Sort of a pain but it shouldn't be a very complex script. - Peter "Steven Shock" wrote: > Exchange 2003 SP2 > > We have a legacy folder hanging around that McAfee created in...