|Jun 13, 2011, 11:59 AM||#1|
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!
2014 15" Retina MacBook Pro 2.5Ghz i7
iPad 2 32gb (white)
iPhone 6+ 64gb (white)
iPhone 5s 16gb
|Jun 13, 2011, 03:46 PM||#4|
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.
Last edited by jaduff46; Jun 13, 2011 at 03:52 PM.
|Thread Tools||Search this Thread|
|thread||Thread Starter||Forum||Replies||Last Post|
|Using Automator to play audio file from within Automator made application?||nobodyhome||OS X 10.8 Mountain Lion||0||Jan 14, 2014 04:23 PM|
|Bootcamp vs. VMWare Fusion 6 vs. Parallels 9: Excel w/large data sets (250,000+ rows)||smileman||Windows, Linux & Others on the Mac||6||Jan 6, 2014 07:13 AM|
|Applescript or automator to open specific file/s||mkorsch||Mac Programming||2||Oct 6, 2013 05:59 AM|
|iOS 7: can't delete or forward specific iMessages?||dagdagdag234||iOS 7||4||Sep 19, 2013 01:43 AM|
|Resolved: Logout hook for specific users | Delete and recreate Library + Ownership||h1068||Mac Programming||4||Jun 16, 2012 11:57 PM|
All times are GMT -5. The time now is 06:04 PM.