alternative to IF statements?

petro

New Member
Joined
Jun 15, 2002
Messages
35
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
680
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
How about using

Select Case....End Select Statement?

GNaga
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
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

Well-known Member
Joined
Jul 10, 2002
Messages
559
Office Version
  1. 365
  2. 2016
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top