• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation

K

kireeti

New Member
Hi

how to validate two columns to get exact value seraing whole of two columns and provide only when Col1, row 2 value = col2, row 2 value.


eg: John=John


I mean my current formula is =if(h1=m1,1,0). I want a formula where H:H=M:M, but must return true only if h1=m1, h2= m2, etc.

I hope I am clear

Thank you
 
Hi,


You could simply add in a "helper" column containing your current formula. You could then sum these up and compare them to the total count. Ie:


John John 1

Mary Mary 1


Sum of 1s = 2. Count of names = 2 therefore true.


Its a bit clunky but should get the result you want.


You may also be able to do this using an array formula.


Myles
 
Kireeta

Try this in any cell except in Column A or B


=+IF(SUMPRODUCT(1*(A1:A65500=B1:B65500))/COUNTA(A1:A65500)=1,"Match","No Match")


But it doesn't work if you make it A:A or even A65536:A65536 ?
 
You must log in or register to reply here.
Back
Top