Hey guys, I know we've got some Excel mavens here. I have a list of stocks (thousand or so) in one column on an Excel spreadsheet. I want to create something that does the following: I pop in a symbol and it either adds it to the list or gives me a response that the symbol is already on this list. I have been just going through the list each time individually, but it's become a bit tedious, and am looking for a more efficient way to do this. Clueless. Thanks in advance for any help... USA
There are many ways to do this: Try either the LOOKUP or VLOOKUP function, if that is too confusing, just sort the column, excel will sort it alphabetically. Best of Luck
Stop the madness!! =MATCH("IBM", A1:A500, 0) Returns row# if found, otherwise returns #NA. Using 0 as last parameter allows using wildcards with text too! If you're inclined to roll your own macro in VBA, this can be used as such... it_lives= Application.WorksheetFunction.MATCH("IBM", A1:A500, 0) Cheers, Osorico
Nick, I know how to sort the columns and find the symbol, however what I need is to have a box on top where I am prompted to enter a symbol and it either adds it to the list or returns that it is already there on the list. THanks for your help.
Edit: I just tried the Match. That was very helpful, now I just need to figure how to get it to add the symbol if not found. Thanks! Thanks for the response! I'll need a little time to abosrb, and then try that. DOn't know anything about VBA programming... Is there a way to take that Match formula and add to it so that if the symbol is not found, it adds the new symbol to the end of the list? Thanks again
First to get to the last blank cell something like this will do the trick: Sub GoToEnd() Application.ScreenUpdating = False Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select End Sub Anyway given that you are at the active cell you can insert the stock. You can also prompt for the new stock to check using Input Box; use online help to get the parameters. To sum here is the sequence in your VBA code: 1. Get input using input box. 2. Use Match to see if it is in the list. 3. If not, then use range(x,y).end(xldown) to get the last row. Then use offset to take you to the next (blank) cell. 4. Finally, set the value in blank cell = to your new stock.
Bernoulli, Thanks, That looks great, but I haven't the slightest on how to program in VB. I think I need it in excel format, or otherwise step by step on how to actually put that together. I very much appreciate the help (not your fault I'm clueless with this!).
Me thinks USAtrader wants someone to write the code and send the spreadsheet to him/her? If you aren't able to piece together the information provided above and figure out some VBA stuff then: www.rentacoder.com www.elance.com etc. Or perhaps some generous ET member will do the work for free! You never know...Bernoulli to the rescue.
Thank you momoney, The Match formula (which was a one-liner), was half of what I'm looking for. So not looking for much here. What I am looking for, really, is something in excel. Was being honest about my lack of any skill/knowledge when it comes to VBA, that's all. Thanks for the attempt at clarifying what I am asking for.
http://www.asap-utilities.com/ Follow this link and install this FREE software. I use it for two years on a daily basis. In this software there is a function that deletes duplicates in a list. So just type in all the symbols and do a check with the function "delete duplicates in list". No programming needed. The best add-in for Excel that i ever saw. 300 additional funtions for free. You can put those you really want to use in a favorites list.