Help me Excel.

October 11, 2007 1:23 PM

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?

posted by guruguy9 to Computers & Internet (5 answers total)

Where will it extract the data to? Another sheet in the file that contains the list of 30 movies? This would be easy (using VLOOKUP()) if you just wanted to pull a single row corresponding to each "movie name," but since you need to pull multiple rows based on a common key field, I'm pretty sure you're going to have to do some VBA.
posted by Doofus Magoo at 1:28 PM on October 11, 2007

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

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

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

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