software

Is it possible to compare two columns of data in Excel. For example if I
have two columns of names A amp; B, with column A containing 5,000 names and
column B containing 1,000 names, how would I compare B to A. Additionally,
would it be possible to then conditionally format the duplicated item in
Column B, maybe make it bold or red?

Thanks

Howard


Refer:
www.j-walk.com/ss/excel/usertips/tip073.htm

Note: You can change the highlighting of differences to highlighting of
duplicates by changing =0 to lt;gt;0 in the formula.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531738
Select column A, Format, Conditional format, Formula is,
=countif(B:B,A1)
then select your cell colour

Select column B, Format, Conditional format, Formula is,
=countif(A:A,B1)
then select your cell colour

--

chief775 Wrote:
gt; Is it possible to compare two columns of data in Excel. For example if
gt; I
gt; have two columns of names A amp; B, with column A containing 5,000 names
gt; and
gt; column B containing 1,000 names, how would I compare B to A.
gt; Additionally,
gt; would it be possible to then conditionally format the duplicated item
gt; in
gt; Column B, maybe make it bold or red?
gt;
gt; Thanks
gt;
gt; Howard--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531738Thanks, to both Bryan and James, this is very helpful. One additional
question -

Is it possible to have the COUNTIF function return 1, instead of a 0, to
indicate that the value in the Newlist already exists in the old list. Then,
instead of changing the formatting have the value which already exists in the
Oldlist deleted from the Newlist?

For example:

Oldlist Newlist
1 3
2 4
3 6
4 1

Ideally, after running COUNTIF()

Oldlist Newlist
1
2
3 6
4I hope that makes sense and thanks for any ideas or help.

Howard

quot;Bryan Hesseyquot; wrote:

gt;
gt; Select column A, Format, Conditional format, Formula is,
gt; =countif(B:B,A1)
gt; then select your cell colour
gt;
gt; Select column B, Format, Conditional format, Formula is,
gt; =countif(A:A,B1)
gt; then select your cell colour
gt;
gt; --
gt;
gt; chief775 Wrote:
gt; gt; Is it possible to compare two columns of data in Excel. For example if
gt; gt; I
gt; gt; have two columns of names A amp; B, with column A containing 5,000 names
gt; gt; and
gt; gt; column B containing 1,000 names, how would I compare B to A.
gt; gt; Additionally,
gt; gt; would it be possible to then conditionally format the duplicated item
gt; gt; in
gt; gt; Column B, maybe make it bold or red?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Howard
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=531738
gt;
gt;


Hi chief775,
I'm bemused how this question relates to the first or what you're
trying to do. Perhaps a new thread with more details so you get a wide
audience?

But I can give an answer to your first question
Is it possible to have the COUNTIF function return 1, instead of a 0
...?
Yes:
--Not()
This formulas surrounding countif (or referenced to a cell containing
the countif formula) will convert false values to true (i.e. 0 or false
to 1) and true values to false (i.e. True or 1 or any non-zero number to
0). The double negative at the start converts the boolean (true/false)
to the number (1/0).

chief775 Wrote:
gt; Thanks, to both Bryan and James, this is very helpful. One additional
gt; question -
gt;
gt; Is it possible to have the COUNTIF function return 1, instead of a 0,
gt; to
gt; indicate that the value in the Newlist already exists in the old list.
gt; Then,
gt; instead of changing the formatting have the value which already exists
gt; in the
gt; Oldlist deleted from the Newlist?
gt;
gt; For example:
gt;
gt; Oldlist Newlist
gt; 1 3
gt; 2 4
gt; 3 6
gt; 4 1
gt;
gt; Ideally, after running COUNTIF()
gt;
gt; Oldlist Newlist
gt; 1
gt; 2
gt; 3 6
gt; 4
gt;
gt;
gt; I hope that makes sense and thanks for any ideas or help.
gt;
gt; Howard
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Select column A, Format, Conditional format, Formula is,
gt; gt; =countif(B:B,A1)
gt; gt; then select your cell colour
gt; gt;
gt; gt; Select column B, Format, Conditional format, Formula is,
gt; gt; =countif(A:A,B1)
gt; gt; then select your cell colour
gt; gt;
gt; gt; --
gt; gt;
gt; gt; chief775 Wrote:
gt; gt; gt; Is it possible to compare two columns of data in Excel. For
gt; example if
gt; gt; gt; I
gt; gt; gt; have two columns of names A amp; B, with column A containing 5,000
gt; names
gt; gt; gt; and
gt; gt; gt; column B containing 1,000 names, how would I compare B to A.
gt; gt; gt; Additionally,
gt; gt; gt; would it be possible to then conditionally format the duplicated
gt; item
gt; gt; gt; in
gt; gt; gt; Column B, maybe make it bold or red?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Howard
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=531738
gt; gt;
gt; gt;--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531738
OK, Think I now understand what you're after

Here's one way:
Assumed your table is in A1 to B5
In C1, enter a heading for your check
In C2 enter
=--NOT(COUNTIF($A$2:$A$5,B2))
Copy down
Whilst in the table select Data-Filter-Autofilter
In the dropdown box in C1 select 0
This highlights all entries in column B to be deleted
Delete these entries in column 2
Dump the filter and column C.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531738