jjxia2001
asked on
need help to write formula in Excel
The attached Excel file is a simple example for my project with 10K rows. "WEST ex CA" and "CENTRAL ex AR" in the drop down box is not working. How to modify the formulas in G4-G9 to make them working? Help needed!
sample.xls
sample.xls
Try this formula in G4 copied down
=($N$2=1)+COUNTIF(INDEX($N $3:$N$7,$N $2),"*"&D4 &"*")*(D4& " ex "&E4<>INDEX($N$3:$N$7,$N$2 ))
regards, barry
=($N$2=1)+COUNTIF(INDEX($N
regards, barry
Use this formula for G4. Then copy G4 and paste in G5:G9.
=IF(OR($N$2=1,INDEX($N$3:$ N$7,$N$2)= LEFT(D4 & " ex " & E4,LEN(INDEX($N$3:$N$7,$N$ 2)))),1,0)
=IF(OR($N$2=1,INDEX($N$3:$
ASKER
Thanks for the effort! I forgot to mention that the real case is a little more complicated, some regions had to exclude more than one state, e.g :"WEST ex CA, AZ, WI". Each region could have up to 15 states. That is why I was struggling to use a formula to cover all of these.
Assuming all excluded states are listed in that format (separated by comma and space) then you can modify my suggestion as follows:
=($N$2=1)+COUNTIF(INDEX($N $3:$N$7,$N $2),D4&"*" )*ISERR(SE ARCH(" "&E4&",",INDEX($N$3:$N$7,$ N$2)&","))
regards, barry
=($N$2=1)+COUNTIF(INDEX($N
regards, barry
If you need more help pls advise
gowflow
gowflow
Barry
Marvellous formulas learned a lot from trying it out !!!
gowflow
Marvellous formulas learned a lot from trying it out !!!
gowflow
ASKER
Barry, it's working perfectly! Thanks so much! Just one last question: only one selection is "ALL ex CA, WI, AL, MN, CO". I guess it can be added as a hard copy, such as if $N$2 = 2, if(or(E4<>"CA", E4<>"WI", E4<>"AL", E4<>"MN", E4<>"CO", then include all.... Can you help me on this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
perfect!
ASKER
sample.xls