Compare 3 columns

March 26, 2010 at 10:27:17
Specs: Windows XP
I have 3 columns with numbers in them. I want to compare all 3 against each other and see if any of them are not exactly the same. If possible put in another column a results that says which column doesn't match. I use exact but it's only for 2 columns. I also made 3 new columns that compares A1, A2 2nd column is A1,A2 and 3rd column compares A2,A3. But there in 3 separate columns. I was thinking of just having results of the comparison into 1 cell. Not sure if it could do it.

See More: Compare 3 columns

Report •


#1
March 26, 2010 at 12:03:04
What do you mean by seeing if "any of them are not exactly the same"?

Exactly the same (in my mind) would be that A1 = B1 = C1 and B2 = B2 = C2 and A3 = B3 = C3, etc.

Put this in D1 and drag it down. If you see any 0's (zeros) then the columns are not exactly the same.

=(A1=B1)*(A1=C1)*(B1=C1)


Report •

#2
March 26, 2010 at 12:51:37
Hi,

Taking DerbyDad03's solution, you can 'pretty it up' by wrapping it in an IF() statement:
=IF((A1=B1)*(A1=C1)*(B1=C1)=0,"Not fully matched","All match")
or this:
=IF(OR(A1="",B1="",C1=""),"Missing number",IF((A1=B1)*(A1=C1)*(B1=C1)=0,"Not fully matched","All match"))
which also tests for a cell with no data entered.

Regards


Report •

#3
March 26, 2010 at 13:58:55
Here is another way.
Put your Data in Columns A - D - G

We are going to use
Columns B & C to check Column D & G
Columns E & F to check Column A & G
Columns H & I to check Columns A & D


In Column B enter the formula:
=IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),"No Match in D","")
Were matching column A to D

In Column C enter the formula:
=IF(ISERROR(MATCH(A2,$G$2:$G$13,0)),"No Match in G","")
Were matching column A to G

In Column E enter the formula:
=IF(ISERROR(MATCH(D2,$A$2:$A$13,0)),"No Match in A","")
Were matching column D to A

In Column F enter the formula:
=IF(ISERROR(MATCH(D2,$G$2:$G$13,0)),"No Match in G","")
Were matching column D to G

I'll leave the last two columns H & I for you to do.

I tried posting an example, but the pre tags would not work.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 31, 2010 at 07:16:01
Thank you everyone. All of those work great!

Report •


Ask Question