Determining repeated cells between two workbooks

[farful]

Hello.

I have three columns in two separate workbooks. I wish to find out which cells are different in workbook B given workbook A.

I'm really not sure how to explain it, so I will provide an example

Workbook A
110 6/20 73
110 7/01 93
110 8/04 82
140 6/15 24
140 7/10 11

Workbook B
110 6/20 99
110 7/03 93
110 7/22 11
110 8/04 82
140 6/15 24

First, I look at the first row from Workbook B. The first column shows 110, so I want to first make sure 110 exists in Workbook A. If it does, then I look at the second column, which is 6/20. I look over at workbook A, and make sure 6/20 exists in the second column where the first column is also 110. (if 6/20 exists, but 110 is NOT in column A, that does NOT count). Then I check out the third column, it shows 99, and try to find 99 in workbook A where column A = 110, and column B = 6/20.
If workbook B shows something different, then I want to highlight that cell.

So in this example: 99, 7/03, 7/22, 11 from Workbook B will be highlighted.

Anyone know of any built-in Excel functions or any VBA way to do this?

Thanks in advance!
(if my question is unclear, please let me know and I will try to clear it up)

[Tom Ogilvy]

Assume Sheet1 and Sheet2. In D1 of sheet1

=if(Sumproduct(--(Sheet2!$A$1:$A$100=A1),--(Sheet2!$B$1:$B$100=B1),(--(Sheet2!$C$1:$C$100=C1))=0,"<==Unique","")

then drag fill down the column.

Similar in Sheet2

If you really need the cells highlighted, then use conditional formatting
with such a formula.

--
Regards,
Tom Ogilvy



"farful" <[email protected]> wrote in
message news:[email protected]...
>
> Hello.
>
> I have three columns in two separate workbooks. I wish to find out
> which cells are different in workbook B given workbook A.
>
> I'm really not sure how to explain it, so I will provide an example
>
> Workbook A
> 110 6/20 73
> 110 7/01 93
> 110 8/04 82
> 140 6/15 24
> 140 7/10 11
>
> Workbook B
> 110 6/20 99
> 110 7/03 93
> 110 7/22 11
> 110 8/04 82
> 140 6/15 24
>
> First, I look at the first row from Workbook B. The first column shows
> 110, so I want to first make sure 110 exists in Workbook A. If it does,
> then I look at the second column, which is 6/20. I look over at workbook
> A, and make sure 6/20 exists in the second column where the first column
> is also 110. (if 6/20 exists, but 110 is NOT in column A, that does NOT
> count). Then I check out the third column, it shows 99, and try to find
> 99 in workbook A where column A = 110, and column B = 6/20.
> If workbook B shows something different, then I want to highlight that
> cell.
>
> So in this example: 99, 7/03, 7/22, 11 from Workbook B will be
> highlighted.
>
> Anyone know of any built-in Excel functions or any VBA way to do this?
>
> Thanks in advance!
> (if my question is unclear, please let me know and I will try to clear
> it up)
>
>
> --
> farful
> ------------------------------------------------------------------------
> farful's Profile:
> http://www.excelforum.com/member.php...o&userid=36172
> View this thread: http://www.excelforum.com/showthread...hreadid=559519
>