Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
I have a dataset that includes a coulmn of product codes-aprox 10'000 entires e.g Prod Code Value 01023456 12 01204895 19 02857867 34 05292453 24 90235905 65 There is other data in differnet columns, with entries relavent to the product codes. As you can see in my example with the value. I have anothe list of codes - aprox 250 (that i call exceptions) that are included in the above table that i want eliminate. Is there a formula to do this e.g Exceptions 01023456 05292453 Thankyou for your help Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
Try use a helper column =MATCH(A1,list of exception,0) If the function finds a match with the list of exception, will return a number otherwise it will return #N/A. Then use Autofilter, custom, does not equal "#N/A", highlight all the rows(should be those you want to delete), press F5, special, visible only, delete them. Hope it helps. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
sorry I'm not really sure how to do this. Could you post an example. Thankyou very much for your help Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
See attachment To turn on autofilter: Tools - Filter - Autofilter Click on the helper column drop down list and select: custom - does not equal & #N/A This will only show all rows where the value under helper column does not equal #N/A. These should be what you want to delete Highlight all visible rows, press F5 and choose: special - visible cells only - OK Now delete these rows +-------------------------------------------------------------------+ |Filename: Sample.zip | |Download: http://www.excelforum.com/attachment.php?postid=4270 | +-------------------------------------------------------------------+ -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
Thankyou this is very helpfull. I have followed your procedure. Could you amend it though. Instead of deleting the exceptions i would like them to be cut and put elsewhere. Is this possibile? Thankyou for your help Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exception deleting
Copy and paste before deleting? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=505319 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows Automatically using a Text File List | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Excel.Range.Name gives error an exception | Excel Discussion (Misc queries) | |||
cell is reinserting a formula in even after deleting it. Why | Excel Discussion (Misc queries) |