PDA

View Full Version : Solved: Something better than IF(OR

stapuff
06-28-2005, 09:20 AM
Column J2-J is a list of (ship from) city's
Column K2-K is the (ship from) state or country

Column M2-M is a list of (ship to) city's
Column N2-N is the (ship to) state or country

In Column L & Column O if the value = US or any of the abbreviated 50 states (WI,IL,CA,NY, etc.) I want it to say Domestic If not International.

How can this be done without building a (or several) super large IF(OR statement(s)?:dunno

Thanks,

Kurt

Zack Barresse
06-28-2005, 09:27 AM
Autofilter your list for the specifications, select the area, press F5/Special/Visible Cells Only, enter your criteria, confirm with Ctrl + Enter. Then again, you could always do this with VBA in a sheet change event. I'd suggest the latter (if a formula is not feasible) only if these will be changing; if static, I'd recommend the first suggestion.

Bob Phillips
06-28-2005, 09:34 AM
Column J2-J is a list of (ship from) city's
Column K2-K is the (ship from) state or country

Column M2-M is a list of (ship to) city's
Column N2-N is the (ship to) state or country

In Column L & Column O if the value = US or any of the abbreviated 50 states (WI,IL,CA,NY, etc.) I want it to say Domestic If not International.

How can this be done without building a (or several) super large IF(OR statement(s)?:dunno

Thanks,

Kurt

Use MATCH. Put the the values WI, IL, etc in a range, say M1:M50, and use

=IF(OR(J2="US",NOT(ISNA(MATCH(K2,M1:M50,0)))),"Domestic","International")

etc.

stapuff
06-28-2005, 11:33 AM
firefytr & xld -

The data will be refreshed on open using the Get External Data feature and the range will be variable (any where from 200 to 10000 rows). My main concern using a formula was the length of time needed to calculate. My concern with using autofilter was the number of filters (26) need to check all 50 states + US. Another thought was using case select.

I am going to try the match formula to see how long it will take.

I appreciate the direction and suggestions you have provided.

Thanks,

Kurt

stapuff
06-30-2005, 11:18 AM
xld -

I need some more help on this formula. I am having an issue.

In column C2:C I have a list of ship froms (abbreviated states / country) and in column J I have the following formula: =IF(OR(C2="US",NOT(ISNA(MATCH(C2,Sheet2!\$A\$2:\$A\$51,0)))),"Domestic","International")

On sheet2 A2-A51 a list of state abbreviations.

I copied the formula in J down. Everything is working as it should.

But......

In column F2:F I have a list of ship to (abbreviated states / country) and in column K I have the following formula: =IF(OR(F2="US",NOT(ISNA(MATCH(F2,Sheet2!\$A\$2:\$A\$51,0)))),"Domestic","International")

The formula was copied down. It shows the result of "International" for every cell. This should not be - so something is failing.

What are your thought or suggestions?

Thanks,

Kurt

Bob Phillips
06-30-2005, 11:35 AM
Nothing spings obviously to mind Kurt.

Obvious things to check. Make sure that F2:F doesn't contain spaces.

I don't get the problem (I wouldn't would I :)).

Only other suggestion, post the workboo fior me to look at.

stapuff
06-30-2005, 12:00 PM
xld -

I think I found the problem.

I am connecting to an access database via Get External Data.

Column C is obviously in an acceptable format for the formula to work.

Column F must not. They look identical, both show as General under format. I copied a abbreviated state value from C and pasted in F then the formula works.

IL in column C results in the formula working in column J
IL in column F results in the formula not working in column K

I copy C to F and the formula works.

I am not even sure in what direction I should go from here.

Kurt

stapuff
06-30-2005, 12:01 PM
XLD

BTW - No leading spaces in either column.

Kurt

Bob Phillips
06-30-2005, 01:06 PM
xld -

I think I found the problem.

I am connecting to an access database via Get External Data.

Column C is obviously in an acceptable format for the formula to work.

Column F must not. They look identical, both show as General under format. I copied a abbreviated state value from C and pasted in F then the formula works.

IL in column C results in the formula working in column J
IL in column F results in the formula not working in column K

I copy C to F and the formula works.

I am not even sure in what direction I should go from here.

Kurt

Check my suggestion about extraneous spaces. Check hat F2:F are only 2 characters.

A suggestion, use a formula of

=IF(OR(F2="US",NOT(ISNA(MATCH(TRIM(F2),Sheet2!\$A\$2:\$A\$51,0)))),"Domestic","International")

stapuff
06-30-2005, 03:33 PM
xld -

Thanks for the post back.

I was able to figure it out. It was a space issue and I noticed you and I came up to the same conclusion.

I noticed when I would click into F2 then click in the formula bar - the cursor was not right next to the value. So I tried adding trim to the formula.

I appreciate all your help and suggestions.

Thanks,

Kurt