Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
Is it possible to compare two columns of data in Excel. For example if I
have two columns of names A & 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
Refer: http://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 <0 in the formula. -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531738 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
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: Is it possible to compare two columns of data in Excel. For example if I have two columns of names A & 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531738 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
Thanks, 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 4 I hope that makes sense and thanks for any ideas or help. Howard "Bryan Hessey" wrote: 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: Is it possible to compare two columns of data in Excel. For example if I have two columns of names A & 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531738 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
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: Thanks, 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 4 I hope that makes sense and thanks for any ideas or help. Howard "Bryan Hessey" wrote: 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: Is it possible to compare two columns of data in Excel. For example if I have two columns of names A & 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531738 -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531738 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing columns of data
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: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531738 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing 2 columns of data | New Users to Excel | |||
combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
comparing columns of data | Excel Worksheet Functions | |||
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) |