Find matching data in two workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

franklinbukoski - 05 May 2008 16:09 GMT

I have data in multiple sheets of one workbook I am trying to match to data
in multiple sheets in another workbook.

The data is contained in various columns a through z and various rows.

Ideally, the data contained in workbook A would be searched for in workbook
B and if found, turn the matching data in workbook B bold red font.

Reply to this Message

Spiky - 05 May 2008 22:08 GMT

On May 5, 10:09 am, franklinbukoski
<franklinbuko...@discussions.microsoft.com> wrote:

> I have data in multiple sheets of one workbook I am trying to match to data
> in multiple sheets in another workbook.
[quoted text clipped - 3 lines]
> Ideally, the data contained in workbook A would be searched for in workbook
> B and if found, turn the matching data in workbook B bold red font.

You want to use conditional formatting, but you can't across sheets/
workbooks. I don't know if there is a slick way, but there is a brute
force way....

Go off to the left of your data in workbook B (assuming your data is
A1:Z100, goto AA1:AZ100) and do a comparison between A1:Z100 in each
workbook. Formula is something like: =EXACT([workbook A]Sheet1!A1,A1),
copy to each cell in AA1:AZ100. It will return TRUE or FALSE for each
cell. Then select the range A1:Z100, do conditional format of bold red
font with the formula: =AA1=TRUE. Make sure to use a relative
reference with no dollar signs so each cell will be done properly.
Should turn all the matches in A1:Z100 to bold red.

You can hide the columns AA:AZ so you don't have to see the
calculation.

Reply to this Message