# 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

#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 - GWe are going to use Columns B & C to check Column D & GColumns E & F to check Column A & GColumns H & I to check Columns A & DIn Column B enter the formula: =IF(ISERROR(MATCH(A2,\$D\$2:\$D\$13,0)),"No Match in D","")Were matching column A to DIn Column C enter the formula:=IF(ISERROR(MATCH(A2,\$G\$2:\$G\$13,0)),"No Match in G","")Were matching column A to GIn Column E enter the formula:=IF(ISERROR(MATCH(D2,\$A\$2:\$A\$13,0)),"No Match in A","")Were matching column D to AIn Column F enter the formula:=IF(ISERROR(MATCH(D2,\$G\$2:\$G\$13,0)),"No Match in G","")Were matching column D to GI'll leave the last two columns H & I for you to do.I tried posting an example, but the pre tags would not work.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
March 31, 2010 at 07:16:01

 Thank you everyone. All of those work great!

Report •