compare cell data

[jef]

I am looking for a formula/function that will compare the data in each
cell in Col A, for example, with each cell in Col B, AND vice versa.
There would ideally be some output to Col C that would alert me that
there is an entry in Col B that is not in Col A, and vice versa. (It
may be that the Col A to Col B "compare" will be output to Col C; and
the Col B to Col A "compare" with be output to Col D.) Or there may
be some other manor of "notification," such as colors in conditional
formatting. Word has this feature in its "Compare and Merge
Documents" feature.

 

[Gord Dibben]

Compare directly across?

i.e. A1 with B1

A2 with B2

A3 with B3

Or anywhere in A or B?


Gord Dibben MS Excel MVP

 

[jef]

Compare directly across?

i.e.  A1 with B1

A2 with B2

A3 with B3

Or anywhere in A or B?

Gord Dibben  MS Excel MVP

For example, compare the contents of A1 with every cell in Col B; cell
A2 will every cell in Col B; etc. And also compare cell B1 with every
cell in Col A. Appears IF and VLOOKUP work only for one cell; that
is, comparing A1 with B1.

 

[jef]

Compare directly across?

i.e.  A1 with B1

A2 with B2

A3 with B3

Or anywhere in A or B?

Gord Dibben  MS Excel MVP

Believe this is the answer: http://www.iaca.net/AnalystToolbox.asp?TID=153

 

[David Heaton]

Believe this is the answer:http://www.iaca.net/AnalystToolbox.asp?TID=153- Hide quoted text -

- Show quoted text -

Jef,

Here is a macro that will do the job for you. go into VBA, insert a
module, then paste the code below.


Sub FindDuplicates()
Dim colARange As Range
Dim colBRange As Range
Dim cRange As Range

Dim LastRowColA As Integer
Dim LastRowColB As Integer

Dim FoundA As Integer
Dim FoundB As Integer

FoundA = 1
FoundB = 1

LastRowColA = Range("a65536").End(xlUp).Row
LastRowColB = Range("b65536").End(xlUp).Row

Set colARange = Range("A1:A" & LastRowColA)
Set colBRange = Range("B1:B" & LastRowColB)

For Each cRange In colARange
If colBRange.Find(cRange) Is Nothing Then
Cells(FoundA, 3) = cRange
FoundA = FoundA + 1
End If
Next

For Each cRange In colBRange
If colARange.Find(cRange) Is Nothing Then
Cells(FoundB, 4) = cRange
FoundB = FoundB + 1
End If
Next

End Sub