compare cell data

  • Thread starter Thread starter jef
  • Start date Start date
J

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.
 
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
 
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
Click to expand...

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.
 
Believe this is the answer:http://www.iaca.net/AnalystToolbox.asp?TID=153- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
Back
Top