Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing columns in different workbooks (2000)

    Is there an easy way to compare information in cells, in two separate columns, in two separate multi-column workbooks?

    I have a list of numbers in certain columns of each Workbook. The numbers should match exactly.

    Workbook (1) Column A:A and Workbook (2) Column C:C should match exactly (not in series order, but just include in each file) . What would be nice is if after "checking" the two files, the font color of those cells that are now inclusive in the other Workbook would be "red" (or something similar to this)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing columns in different workbooks (2000)

    Say that your numbers are in A1:A100 in one worksheet, and in C1:C100 in the other one. If necessary, insert a blank column to the right of column A in the first worksheet. In cell B1, enter this formula:

    <code>=COUNTIF('[Otherworkbook]SheetName'!$C$1:$C$100,A1)</code>

    where Otherworkbook is the name of the other workbook, and SheetName is the name of the worksheet with numbers in C1:C100. Fill down to B100. Column B will be zero if there is no match, 1 if there is a unique match and >1 if there are duplicate matches. If you wish, you can use this column in conditional formatting:
    - Select A1:A100
    - Select Format | Conditional Formatting...
    - Select Formula Is in the dropdown list.
    - Enter =B1 in the box next to it.
    - Click Format... and specify the desired formatting.
    - Click OK twice.
    You might now hide column B.

    It's similar for the other workbook.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing columns in different workbooks (2000)

    Hans

    Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •