software

I have a spread sheet as such:

column 1 column 2

a a
b b
c e
d

What I need to do is compare the 2 columns and figure out which entries are
unique to both columns and which are only in column1 or column 2. The data
will be alphanumeric.

One visual way of achieving this is to use conditional formatting to
highlight which entries in column 1 also appear in column 2 (and vice
versa), so that the non-highlighted cells are obviously unique in their
respective column. Let's assume that your list in column A extends from
A1 to A200, and in B from B1 to B150. Highlight the data in column A,
then select Format | Conditional Formatting ... In the panels presented
to you, select quot;Cell Value Isquot; and quot;equal toquot; and in the third panel
enter the formula:

=VLOOKUP(A1,$B$1:$B$150,1,0)

Then click on the Format button and select as appropriate (eg Patterns
{i.e. background colour} then select yellow). Then click OK. This will
give a yellow background to any value in column A which is also present
in column B.

Then highlight the data in column B, and select Format | Conditional
Formatting ... again. This time the formula needs to be:

=VLOOKUP(B1,$A$1:$A$200,1,0)

and choose the same yellow background as before, then click OK.

Hope this helps.

PeteUse conditional formatting with formula of

=COUNTIF($B:$B,A1)gt;0

and

=COUNTIF($A:$A,B1)gt;0

in column A and B respectively

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Joshuaquot; gt; wrote in message
...
gt; I have a spread sheet as such:
gt;
gt; column 1 column 2
gt;
gt; a a
gt; b b
gt; c e
gt; d
gt;
gt; What I need to do is compare the 2 columns and figure out which entries
are
gt; unique to both columns and which are only in column1 or column 2. The
data
gt; will be alphanumeric.
You can use conditional formating

Select the cells in column A you want to apply the format to
Click Format-gt;Conditional Formatting
Select quot;formula isquot; and enter one of these formulas
=COUNTIF($B:$B,A1)=0
=COUNTIF($B:$B,A1)=1
=COUNTIF($B:$B,A1)gt;1
Select an obvious formatting like a yellow background color.
Click on Okay

Do the same for column B except use these formulas
=COUNTIF($A:$A,B1)=0
=COUNTIF($A:$A,B1)=1
=COUNTIF($A:$A,B1)gt;1

The first formula returns true if the cell value can not be found in the list
The second formula returns true if the cell value can be found once in the
list
The third formula returns true if the cell value can be found multiple times
in the list

You can have up to three conditional formats, so you can apply different
colors for each case.You can also insert similar formulas in helper columns
=COUNTIF(B:B,A1)
=COUNTIF(A:A,B1)

this will return the number of times each cell is in the list.

Example:
aa11
bb11
ce10
dc01

quot;Joshuaquot; wrote:

gt; I have a spread sheet as such:
gt;
gt; column 1 column 2
gt;
gt; a a
gt; b b
gt; c e
gt; d
gt;
gt; What I need to do is compare the 2 columns and figure out which entries are
gt; unique to both columns and which are only in column1 or column 2. The data
gt; will be alphanumeric.

One way, via non-array formulas ..

A sample construct is available at:
cjoint.com/?ccdO3RQFok
Finding unique data between 2 cols_Joshua_wks.xls

Source data in cols A and B, from row1 down as posted

In D1:
=IF(ISERROR(SMALL(E:E,ROW(A1))),quot;quot;,
INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

In E1:
=IF(A1=quot;quot;,quot;quot;,IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),quot;quot; ))

In F1:
=IF(ISERROR(SMALL(G:G,ROW(A1))),quot;quot;,
INDEX(A:A,MATCH(SMALL(G:G,ROW(A1)),G:G,0)))

In G1:
=IF(A1=quot;quot;,quot;quot;,IF(ISNUMBER(MATCH(A1,B:B,0)),quot;quot;,ROW() ))

Select D1:G1, fill down until the last row of data in col A

Col D returns items common to both cols A and B
Col F returns items in col A not found in col B
(Results will be neatly bunched at the top)

In H1:
=IF(ISERROR(SMALL(I:I,ROW(A1))),quot;quot;,
INDEX(B:B,MATCH(SMALL(I:I,ROW(A1)),I:I,0)))

In I1:
=IF(B1=quot;quot;,quot;quot;,IF(ISNUMBER(MATCH(B1,A:A,0)),quot;quot;,ROW() ))

Select H1:I1, fill down until the last row of data in col B

Col H returns items in col B not found in col A
(Results will be neatly bunched at the top)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Joshuaquot; gt; wrote in message
...
gt; I have a spread sheet as such:
gt;
gt; column 1 column 2
gt;
gt; a a
gt; b b
gt; c e
gt; d
gt;
gt; What I need to do is compare the 2 columns and figure out which entries
are
gt; unique to both columns and which are only in column1 or column 2. The
data
gt; will be alphanumeric.