Microsoft Excel 2010 - complete product...

I am trying to verify that three columns (A, B, and C) contain the same numbers. I first arranged the numbers in each column in ascending order. I used "=exact" to verify that the numbers are the same by comparing A to B, B to C, and A to C. Is there a way that I can verify the numbers in all three columns in one procedure? Thank you.

Brian W

The simplest way that I can think of, is to separate your three columns so there is a blank column between them and to be completely thorough you will need to to 6 comparisons: First A to C,

then C to A, (item can be in C but not in A)then C to E,

then E to C,then E to A,

then A to E,With your data like:

A B C D E F 1) 1500001 1400014 1500001 2) 1500002 1500001 1500002 3) 1500003 1500002 1500003 4) 1500004 1500003 1500004 5) 1500005 1500004 1500005 6) 1500006 1500005 1500006 7) 1500007 1500006 1500007 8) 1500008 1500007 1500008 9) 1500009 1500008 1500009 10) 1500010 1500009 1500016 11) 1500011 1500010 1500011 12) 1500012 1500011 1500012 13) 1500013 1500012 1500013 14) 1500014 1500013 1500014Now in column B enter the formula:

=IF(ISERROR(MATCH(A1,$C$1:$C$14,0)),"A No Match C",IF(ISERROR(MATCH(C1,$A$1:$A$14,0)),"C No Match A",""))

We first check column A against Column C

then Column C to AIn column D enter the formula:

=IF(ISERROR(MATCH(C1,$E$1:$E$14,0)),"C No Match E",IF(ISERROR(MATCH(E1,$C$1:$C$14,0)),"E No Match C",""))

We check C against E then E against C

Finally, in column F enter the formula:

=IF(ISERROR(MATCH(E1,$A$1:$A$14,0)),"E No Match A",IF(ISERROR(MATCH(A1,$E$1:$E$14,0)),"A No Match E",""))We check E against A then A against E

You should end up with something like:

A B C D E E 1) 1500001 C No Match A 1400014 C No Match E 1500001 2) 1500002 1500001 1500002 3) 1500003 1500002 1500003 4) 1500004 1500003 1500004 5) 1500005 1500004 1500005 6) 1500006 1500005 1500006 7) 1500007 1500006 1500007 8) 1500008 1500007 1500008 9) 1500009 1500008 1500009 10) 1500010 1500009 E No Match C 1500016 E No Match A 11) 1500011 1500010 C No Match E 1500011 12) 1500012 1500011 1500012 13) 1500013 1500012 1500013 14) 1500014 A No Match C 1500013 E No Match C 1500014There is also a Conditional Formatting solution

as well as a =VLOOKUP solution.For the =VLOOKUP see the YouTube video:

http://www.youtube.com/watch?v=uVOv...

There are undoubtedly others.

MIKE

Without knowing what you have as data, but just going by the fact that you said you used EXACT to compare "A to B, B to C, and A to C", it sounds like you what to check your data row by row to see if all three values in that row are the same. If it's as simple as that, use this in e.g. D1 and drag it down:

=IF(AND(A1=B1,A1=C1),1,0)

Obviously, you can replace the 1 and 0 with anything that you want, but by using those values, you can check a single cell to see if any rows don't match instead of visually looking for 0's.

Put this in e.g. E1 and you'll know whether you need to go searching or not:

=COUNTA(D1:D2000)-SUM(D1:D2000)&" Rows Don't Match"

If you don't see

0 Rows Don't Matchyou'll know you have to go looking for 0's.

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

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History