alternative to IF statements?

[petro]

Hi everyone,

As part of the cleanup portion of a larger macro I'm creating, I need to add a 3 letter extension to an ID number for a stock. The problem I have is that the 3 letter extension is different depending on which exchange around the world the stock is traded on. The way the spreadsheet is setup, column A would be the exchange and column B the ID number. I originally thought of doing an IF statement that would go loop through the spreadsheet something like...

If column A = "toronto" then add .tor to column B
Else If A = "Paris" then add .par to column B

and so on. I know the code to add the extension to column B, my problem is that I have a list of about 25 exchanges. Since Excel limits me to only 7 nested IF's, I'm obviously over my limit. The spreadsheet would have up anywhere from 500 to about 2000 securities on it, so to have it loop through multiple times with each loop having 7 IF statements, would slow it down considerably. Would anyone have any suggestions as to an alternate plan of attack?

 

[gnaga]

How about using

Select Case....End Select Statement?

GNaga

 

[bolo]

how about creating another column and using the concatenate functioneg. in column c:

=B1 & left (A1,3)

this adds the first three letters from A1 onto B1. If this doesn't work you could use lookup tables or write code.

 

[chef]

You could use lookup..

example

A2 = Rome
A3 = Edinburgh
A4 = Toronto
A5 = Milan
A6 = Barcelona
A7 = Sidney
A8 = London
A9 = Paris

B2 = Rom
B3 = Edi
and so on down to B8

in cell c2 down to c8 type in your data..in this case type in Paris London etc..

in cell d2 type in
=IF(ISNA(VLOOKUP(C2,$A$2:$B$8,2,0)),"",VLOOKUP(C2,$A$2:$B$8,2,0))

copy down to cell c8

this will give you you abbrieviated code.

I would then concatate this with your invoice number with the abb code

Might be an easier way with VBA but this works for me

Regards
Chef

 

[Aladin Akyurek]

On 2002-10-03 08:00, petro wrote:
Hi everyone,

As part of the cleanup portion of a larger macro I'm creating, I need to add a 3 letter extension to an ID number for a stock. The problem I have is that the 3 letter extension is different depending on which exchange around the world the stock is traded on. The way the spreadsheet is setup, column A would be the exchange and column B the ID number. I originally thought of doing an IF statement that would go loop through the spreadsheet something like...

If column A = "toronto" then add .tor to column B
Else If A = "Paris" then add .par to column B

and so on. I know the code to add the extension to column B, my problem is that I have a list of about 25 exchanges. Since Excel limits me to only 7 nested IF's, I'm obviously over my limit. The spreadsheet would have up anywhere from 500 to about 2000 securities on it, so to have it loop through multiple times with each loop having 7 IF statements, would slow it down considerably. Would anyone have any suggestions as to an alternate plan of attack?

If you are using the first 3 letter of every exchange, you could use in column B the formula...

=A1&"."&LEFT(A1,3) or just ="."&LEFT(A1,3)

If this regularity does not exist, you can create a 2-column list of exchanges and extensions, which you can name Extenstions. Then you can use in column B...

=A1&"."&VLOOKUP(A1,Extensions,2,0)

or just

="."&VLOOKUP(A1,Extensions,2,0)