Excel Help

U

USAtrader

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
 
Quote from USAtrader:

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
More...

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
 
Quote from Nick Leeson Jr:

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
More...

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.
 
Quote from osorico:

Stop the madness!!.....
Osorico
More...

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.

Quote from momoneythansens:

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...
More...
 
Quote from USAtrader:

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
More...

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.
 
You must log in or register to reply here.
Back
Top