Solved Macro on Excel 2013 to confirm if Gx = Hx?

March 9, 2016 at 11:43:04
Specs: Windows 7
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


See More: Macro on Excel 2013 to confirm if Gx = Hx?

Report •

#1
March 9, 2016 at 13:58:12
✔ Best Answer
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 it looks blank 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
March 9, 2016 at 14:08:46
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.


Report •

#3
March 9, 2016 at 14:45:41
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


Report •
Related Solutions


Ask Question