Comparing Columns of no particular order
Posted by Tim on February 11, 2002 9:05 AM
I have two sheets (A and B), with a similar but not identical column in both (namely file names). I need to know which file names are present in both sheets, and produce a list of those files (with all the associated data on both sheets) and simialr lists for those that are not repeated.
I KNOW this must be a relativly simply proceedure in Excel, but I have resorted to comparing them all by hand, which is really starting to numb my brain!
Thanks for any help you can give,
Tim
Posted by Yogi Anand on February 11, 2002 9:29 AM
Hi Tim:
If you could highlite the cells in one sheet -- then
EDIT|Go_to_Special|Row_differences
would mark the entries that are different in the associated row. I know your data is in different worksheets. You may have to do some manouvering to make it work. Please post back whether it works for you!
Yogi Anand
ANAND Enterprises www.handtech.com/anand
Posted by Tim on February 11, 2002 10:17 AM
Thanks ... that method NEARLY worked, but it only highlighted those cells that were the same when lined up already.
Here is an example of two of the columns ... with those matching cells highlighted with an *.
A B
INT:CO44X * INT:200063
INT:LM92I INT:970252
INT:ME87I INT:990122_COPY2
INT:WA92C INT:990122_COPY
INT:WA92RC INT:CO44X *
INT:WAPBC INT:KINGZERO
MIS:2316IR INT:P89050
MIS:AH29I INT:P94852
I need to be able to look at what is in column A and see if it is anywhere in column B, and to mark it in some way.
Any ideas?
Posted by Tim on February 11, 2002 10:18 AM
Sorry - that should read ...
Posted by Tim on February 11, 2002 10:19 AM
I can't get this to show how I want it to - hope you might be able to make sense of it!
Posted by Yogi Anand on February 11, 2002 11:49 AM
Posted by Aladin Akyurek on February 11, 2002 11:53 AM
See...
Posted by Yogi Anand on February 11, 2002 12:17 PM
Hi Tim:
I am not quite sure what you are trying to do -- but it appears you need to work with Advanced Filter ... DATA|FILTER
col1 col2
criteria---> INT:CO44X
INT:CO44X
----source data ------
col1 col2 col1 col2
INT:CO44X INT:200063 INT:CO44X INT:200063
INT:LM92I INT:970252 INT:WA92RC INT:CO44X
INT:ME87I INT:990122
INT:WA92C INT:990122 --- output above -----
INT:WA92RC INT:CO44X
INT:WAPBC INT:KINGZERO
MIS:2316IR INT:P89050
MIS:AH29I INT:P94852
HTH
Yogi Anand
ANAND Enterprises www.handtech.com/anand
Posted by Aladin Akyurek on February 11, 2002 12:19 PM
How about...
using COUNTIF or MATCH to flag matches between the two columns?
Lets suppose that the first set of filenames is in column A in Sheet1 and the second set in column A in Sheet2.
In Sheet1:
in B1 enter: =(COUNTIF(Sheet2!A:A,A1) > 0)+0
and drag this down as far as needed.
If so desired, select the relevant cells in B and custom format the selection (via Format|Cells) as
[=1]"*";[=0]"";General
Follow the above proc, if needed, in Sheet2.
========
Posted by Sorted on February 12, 2002 8:26 AM
Re: See...
Thanks so much - I have sorted it out now using COUNTIF
MAny thanks for your time and effort.