Hello, I need a macro that checks if the contents if G(row) = H(same row). I really don't know how to do it.

Specfically:

If H=1, G is supposed to equal 1 or greater. In this case, this would be a correct response, and I would want the macro to make column I (same row) = correct.

If H=1 and G=0, this is a missed response. In this case, I would want the macro to make column I = missed.

If H=0, G is supposed to be empty. If this is the case, I want column I to remain empty. If column G equals anything else when column H=0, this is a false positive. In this case, I would want the macro to make column I = false positive.

Can anyone write this? I wouldn't even know where to start and I have over 4000 rows of data. It's for my thesis... I can't do it without a macro because there would clearly be human error. :(

Thank you!!!

Eva

message edited by ebest

Don't think you need a Macro, try this formula in cell I1 =IF(AND(H1=1,G1=0),"Missed",IF(AND(H1=1,G1>=1),"Correct",IF(AND(H1=0,G1=""),"",IF(AND(H1=0,G1>=0),"False Positive",""))))

Drag down as many rows as needed.

EDIT ADDED:

One caveat about H1=0 and G1="",

Excel considers a SPACE a valid character,

so even if itlooksblank it may not be.

There may be a Space character inhabiting the cell.Also, what happens if H1 = 1 and G1 = "" <blank>

or can this never occur?MIKE

message edited by mmcconaghy

re: " I can't do it without a macro because there would clearly be human error"Why do you feel that you need a macro? Why can't you use a Nested IF formula?

=IF(AND(H1=1,G1>=1),"Correct",IF(AND(H1=1,G1=0),"Missed",

IF(AND(H1=0,G1<>""),"False Positive","")))Whether you use this formula or a macro, you have at least one condition that you have not accounted for:

What happens if H1 = 1 and G1 is Empty?

As written, the formula will return "Missed" but you haven't told us what it should return for that case.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Mike, I'm afraid it didn't work. It seems there are missing qualifiers in the formula. It's all good, though, I found a way to avoid human error. I was just coming on to say this was solved. Thank you SO much for having tried to find a solution. You are one of the good ones. :)

Best wishes,

Eva

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History