Macro to compare two columns of data
 

Hello All Excel Guru's:

I was wondering if it was possible to have a macro to compare two
columns of data and in the third column only show the ones that are in
Column B and not in Column A and vice versa, In Column A but not in
Column B in Column D

Examle:

Column A Column B Column C Column D

Apple Apple
Bananas Bananas Strawberry
Orange Pear Pear
Grapes Orange Cherry
Strawberry Grapes
Cherry Plums Plums

I have a formula that does this, but I have to copy this formula down
Column C. The
formula that I have is listed below:

formula located in Column C
=IF(B3:B101="","",IF(ISERROR(VLOOKUP(B3,$A$1:$A$20 00,1,FALSE)),B3,""))

formula located in Column D
=IF(A3:A101="","",IF(ISERROR(VLOOKUP(A3,$B$1:$B$20 00,1,FALSE)),A3,""))

Is there anyway to have a macro do the same thing, except that have
all the items in column C and column D sorted together?

Any and all help in this matter is greaty appreciated.

Argus

Why not just apply the same formulas in code, convert to values and sort the
column.

Option Explicit
Sub testme01()

Dim ColARng As Range
Dim ColBRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set ColARng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set ColBRng = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))

With ColARng.Offset(0, 2)
.FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC[-2],C[-1],0)),"""",RC[-2])"
.Value = .Value
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

With ColBRng.Offset(0, 2)
.FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC[-2],C[-3],0)),"""",RC[-2])"
.Value = .Value
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
End With
End Sub

I used match() instead of =vlookup() to see if there was a match.


Odawg wrote:

Hello All Excel Guru's:

I was wondering if it was possible to have a macro to compare two
columns of data and in the third column only show the ones that are in
Column B and not in Column A and vice versa, In Column A but not in
Column B in Column D

Examle:

Column A Column B Column C Column D

Apple Apple
Bananas Bananas Strawberry
Orange Pear Pear
Grapes Orange Cherry
Strawberry Grapes
Cherry Plums Plums

I have a formula that does this, but I have to copy this formula down
Column C. The
formula that I have is listed below:

formula located in Column C
=IF(B3:B101="","",IF(ISERROR(VLOOKUP(B3,$A$1:$A$20 00,1,FALSE)),B3,""))

formula located in Column D
=IF(A3:A101="","",IF(ISERROR(VLOOKUP(A3,$B$1:$B$20 00,1,FALSE)),A3,""))

Is there anyway to have a macro do the same thing, except that have
all the items in column C and column D sorted together?

Any and all help in this matter is greaty appreciated.

Argus

--

Dave Peterson