Printable View
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
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.
Use MATCH. Put the the values WI, IL, etc in a range, say M1:M50, and useQuote:
Originally Posted by stapuff
etc.Code:=IF(OR(J2="US",NOT(ISNA(MATCH(K2,M1:M50,0)))),"Domestic","International")
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
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:On sheet2 A2-A51 a list of state abbreviations.Code:=IF(OR(C2="US",NOT(ISNA(MATCH(C2,Sheet2!$A$2:$A$51,0)))),"Domestic","International")
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:The formula was copied down. It shows the result of "International" for every cell. This should not be - so something is failing.Code:=IF(OR(F2="US",NOT(ISNA(MATCH(F2,Sheet2!$A$2:$A$51,0)))),"Domestic","International")
What are your thought or suggestions?
Thanks,
Kurt
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.
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
XLD
BTW - No leading spaces in either column.
Kurt
Check my suggestion about extraneous spaces. Check hat F2:F are only 2 characters.Quote:
Originally Posted by stapuff
A suggestion, use a formula of
Code:=IF(OR(F2="US",NOT(ISNA(MATCH(TRIM(F2),Sheet2!$A$2:$A$51,0)))),"Domestic","International")
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