compare two columns
Posted by scott on December 17, 2001 9:07 AM
does anyone know how I can compare values in two columns to see if they contain the same values? For example, I have two columns of data that have 2000 rows each, and I want to know where the inconsistencies are (ie. A1 should = B1, A2 should = B2, etc.) Any way to do this? thanks!
Posted by Travis on December 17, 2001 9:22 AM
Just a thought
I usually put a formula in column C that is:
=if(A1<>B1,1,"")
Then sort by column C to get all the 1's together, or do a search for 1's
Also if the sum of column C is 0 then there are no problems.
Posted by Damon Ostrander on December 17, 2001 9:29 AM
Hi Scott,
You didn't mention whether your data were numeric. If they are here is a simple formula that returns zero if the two ranges match, and gives you a positive squared difference number if they don't.
=SUMXMY2(A1:A2000,B1:B2000)
Happy computing.
Damon
Posted by Tom Urtis on December 17, 2001 9:34 AM
And one more option, in addition to Travis' and Damon's
If you don't want to involve another column and see the difference easily, another option is conditional formatting.
Select the first cell (A1 in your case) and click Format > Conditional Formatting, then select Formula is, and type in =A2<>B2. Then click the Format button, and maybe the Patterns tab, choose a bright color like red, and OK, OK. Then copy A1, and paste special values, for Formats, for the rest of your data in column A.
Tom Urtis
Posted by Tom Urtis on December 17, 2001 9:36 AM
OUCH !! Typo Alert !!
Sorry, typo in formula should be =A1<>B1 if your data starts in A1.
Tom U
: Hi Scott,
