Vba code

[Andrews Felix]

Hi

Please help me with this, if column A is yes, and column B is advance and column c is <0.5 then D= good. And if A is yes, B is advanced and c is >500 and less than or equal to 1 then it is great. And if A is yes and b is advanced and c is greater than 1 or less than or equal to 2 then it is best. I tried with if formula, but its taking only one argument at a time. Could some one help me with a vba code. Code which applies for whole D column not only till D9

 

[steve the fish]

How can C be greater than 500 and less than or equal to 1? Formula would be easy enough but that doesnt make sense.

 

[Andrews Felix]

How can C be greater than 500 and less than or equal to 1? Formula would be easy enough but that doesnt make sense.

Sorry for that, i meant c is >0.5 and less than or equal to 1

 

[steve the fish]

Ok so you now have another problem. What if c is exactly 0.5? What is that supposed to produce?

 

[Andrews Felix]

Ok so you now have another problem. What if c is exactly 0.5? What is that supposed to produce?

It's same as <0.5. if c is <= 0.5 then its good. If formula doesn't considering my second argument, i dont know why, im new and learning, any help will be appreciated. Thanks in advance

 

[steve the fish]

This is a formula you could use:

=IF(AND(A1="Yes",B1="Advanced"),IF(C1<=0.5,"Good",IF(AND(C1>0.5,C1<=1),"Great",IF(AND(C1>1,C1<=2),"Best",""))),"")

 

[Andrews Felix]

Thank you so much steve! It worked. Will this work for 50+ possibilities?

This is a formula you could use:

=IF(AND(A1="Yes",B1="Advanced"),IF(C1<=0.5,"Good",IF(AND(C1>0.5,C1<=1),"Great",IF(AND(C1>1,C1<=2),"Best",""))),"")

 

[Kaper]

Probably the formula similar to one above will be too complicated.

But there are also other formulas - may be some combination of VLOOKUP, may be INDEX and MATCH, etc. could be used. But the solution depends on
How many columns will be examined? 3? how many of them have "clearly defined values (like your A: "Yes" and "No". And how many os such possibilities (i.e. is "May be" also possible in that colun - and what to do if such value is met. How many ranges you will have in numerical values column, and will these ranges be contignous (like from 0 to 0.5 (inclusive), from 0.5(not incl) to 1 (incl), etc. or 0..0.5 but then 1..2 and again what if below 0 and what if above maximum defined value met.

As for VBA code as suggested by title we could suggest something, but will you be able to adopt it to your needs? If no detailed description is given - I doubt, because the code will not fit your situation.

 

[Andrews Felix]

Probably the formula similar to one above will be too complicated.

But there are also other formulas - may be some combination of VLOOKUP, may be INDEX and MATCH, etc. could be used. But the solution depends on
How many columns will be examined? 3? how many of them have "clearly defined values (like your A: "Yes" and "No". And how many os such possibilities (i.e. is "May be" also possible in that colun - and what to do if such value is met. How many ranges you will have in numerical values column, and will these ranges be contignous (like from 0 to 0.5 (inclusive), from 0.5(not incl) to 1 (incl), etc. or 0..0.5 but then 1..2 and again what if below 0 and what if above maximum defined value met.

As for VBA code as suggested by title we could suggest something, but will you be able to adopt it to your needs? If no detailed description is given - I doubt, because the code will not fit your situation.

Hi,

Thank you for the reply. 4 columns will be examined and totally there are 23 columns are there but only 4 will be examined. Out of 4, 3 has clearly defined values and 1 column has integers like <0.5, 0.5to 1, 1to2, 2 to 3, 3 to 4 like this till 9 to 10. I will check with index and match like you suggested. If there are any code you can suggest, it will be appreciated!

 

[Kaper]

OK, so data from these 3 well defined can be easily combined (concatenated) with & operator and used as headers of columns
then limiting valueas for numeric column can be used for headers of rows and finally you can use

= INDEX(table_with_values,MATCH(value,row_headers,1),MATCH(column_defined1&colun_defined2&columndefined3,columns_headers,0))