Help me Excel.
October 11, 2007 1:23 PM Subscribe
Please help me create an expandable excel function that can find and extract cell rows.
I'd like to create a list of phrases in an excel function/macro that I can update on a monthly basis and then use it to retrieve data from another excel sheet that is updated by several people, several times a month.
For example - I'd make a list of 30 movies and then run the function that will extract every row with that movie name (out of thousands) from a different list and collate them on a separate sheet.
Or something like that. Make sense?
I'd like to create a list of phrases in an excel function/macro that I can update on a monthly basis and then use it to retrieve data from another excel sheet that is updated by several people, several times a month.
For example - I'd make a list of 30 movies and then run the function that will extract every row with that movie name (out of thousands) from a different list and collate them on a separate sheet.
Or something like that. Make sense?
Best answer: I'll also point out (having now noticed your use of the "database" tag) that if you did this in Access, it would be a piece of cake. You create one table in the database containing your list of 30 movies, then create a "Linked Table" to the other Excel file, and then create a query joining the two based on the movie name.
posted by Doofus Magoo at 1:31 PM on October 11, 2007
posted by Doofus Magoo at 1:31 PM on October 11, 2007
Response by poster: Extracting to another sheet in the list of 30 movies would be just fine. Any pointers in visual basic?
posted by guruguy9 at 1:34 PM on October 11, 2007
posted by guruguy9 at 1:34 PM on October 11, 2007
Try the free version of ASAP Utilities for Excel. It installs as another menu on the "File" toolbar and has helpful macros. To your point above, you can have Column A with your personal list of 30 favorites. Column B can contain 1,000+ generic list of movies. ASAP has a macro that will highlight duplicates a specific color. The new sheet creation would have to be manual but this will save you some time:
http://www.asap-utilities.com/download-asap-utilities.php
Otherwise, it sounds like you'll want to use the =vlookup() function in excel. A quick google for examples will point you in the richt direction.
Disclaimer: I don't know the rules around AskMeFi about promoting 3rd party software. I'm assuming that since I am in no way affiliated with this software it is ok. Please excuse me and kindly let me know if I'm breaking protocol. I come in peace!
posted by thankyoumuchly at 1:55 PM on October 11, 2007
http://www.asap-utilities.com/download-asap-utilities.php
Otherwise, it sounds like you'll want to use the =vlookup() function in excel. A quick google for examples will point you in the richt direction.
Disclaimer: I don't know the rules around AskMeFi about promoting 3rd party software. I'm assuming that since I am in no way affiliated with this software it is ok. Please excuse me and kindly let me know if I'm breaking protocol. I come in peace!
posted by thankyoumuchly at 1:55 PM on October 11, 2007
Response by poster: Thank you both for the help! I was looking around vlookup() as well as Access (hey that's installed on my computer!) and actually have it working perfectly in access after I determined how to set the file relationships.
Thanks again!
posted by guruguy9 at 3:49 PM on October 11, 2007
Thanks again!
posted by guruguy9 at 3:49 PM on October 11, 2007
This thread is closed to new comments.
posted by Doofus Magoo at 1:28 PM on October 11, 2007