Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Jun 13, 2011, 10:59 AM   #1
AFPoster
macrumors 65816
 
Join Date: Jul 2008
Location: Charlotte, NC
Automator to delete specific Excel rows

I would like to know if this is possible and if anyone can tell me how to get this setup.

I have an excel document with 4 columns and over 16,000 rows. In Column A & B is an ID # and a Ticker. In C & D is a Ticker and an ID Number. I am basically trying to match the tickers together. So C&D columns go to 19,000 and A & B goes to 16,000. I have to remove 3,000 from the list. I've done this already but we lost the file so I am starting from scratch again. However I have the list of removed tickers on another document. So I know what needs to be removed.

The question: Is there any way to create an Automator script to remove the specific cells that I give the ticker for in just Column C & D. Seeing as I have the same tickers between B&C I don't want B to be touched.

I hope this makes sense, thanks!
__________________
2012 15" MacBook Pro 2.3Ghz i7
iPad 2 32gb (white)
iPhone 5s 16gb x2
AFPoster is offline   0 Reply With Quote
Old Jun 13, 2011, 12:04 PM   #2
waynep
macrumors 6502
 
Join Date: Dec 2009
Would probably be easier to write Visual Basic code inside Excel for this. I am learning some VB automate some Excel functions.
waynep is offline   0 Reply With Quote
Old Jun 13, 2011, 02:08 PM   #3
sero
macrumors member
 
Join Date: Aug 2008
if you can save as csv (or tsv, etc), this could be done with awk in terminal
Code:
man awk
sero is offline   0 Reply With Quote
Old Jun 13, 2011, 02:46 PM   #4
jaduff46
macrumors newbie
 
Join Date: Mar 2010
Well, agree with Sero that awk would work. Join would also work if you have the UNIX utilities.

In Excel you can use MATCH and/or VLOOKUP. Syntax is MATCH(longer list ID, shorter ID list). If the longer list ID is in the shorter ID list, it will return the row number in the shorter ID list of the longer list ID, otherwise it will return #N/A.

VLOOKUP works in similar fashion, but allows you to grab another field from the shorter list (i.e. 2nd column) if there's a match on the id. If the shorter list is unordered, use FALSE as the last argument in the vlookup call.

All the rows with #N/A returned are rows in the longer list with no match in the shorter list. Sort the longer list by whether the function returned a match or #N/A, which would put all the unmatched rows in the longer list together to be deleted.

Have fun,

Last edited by jaduff46; Jun 13, 2011 at 02:52 PM.
jaduff46 is offline   0 Reply With Quote
Old Jun 13, 2011, 02:53 PM   #5
jaduff46
macrumors newbie
 
Join Date: Mar 2010
!

Last edited by jaduff46; Jun 14, 2011 at 07:10 AM.
jaduff46 is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
thread Thread Starter Forum Replies Last Post
Resolved: Automator to Detect Drive Jamesking56 Mac Programming 2 Oct 29, 2011 07:30 PM
How To Delete In Excel ghsNick Mac Applications and Mac App Store 2 Jul 20, 2011 12:46 PM
How can I set up an automation to run a specific program and THEN assign a hotkey? eroxx OS X 1 May 12, 2011 08:45 PM
Way to delete podcast downloads from the Downloads window? BlueberryMac Mac Applications and Mac App Store 1 Mar 28, 2011 05:24 AM
Automator to delete files based on date sweileman Mac Applications and Mac App Store 1 Apr 30, 2010 07:47 AM


All times are GMT -5. The time now is 09:24 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC