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

Any suggestions would be helpful.:help

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.

xld
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

Any suggestions would be helpful.:help

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 -

Thanks for your suggestions.

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

xld
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

xld
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