Solved Verifiy that three columns contain the same numbers.

Microsoft Excel 2010 - complete product...
July 20, 2013 at 06:32:16
Specs: Windows XP Pro SP3, 3.0 Mz/1.0Gb
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


See More: Verifiy that three columns contain the same numbers.

Report •


#1
July 20, 2013 at 10:50:15
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                   1500014

Now 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 A

In 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  1500014	

There 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

http://www.skeptic.com/


Report •

#2
July 20, 2013 at 13:06:22
✔ Best Answer
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 Match you'll know you have to go looking for 0's.

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


Report •

Related Solutions


Ask Question