Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro that will add data from multiple workbooks to the 1st open r | Excel Discussion (Misc queries) | |||
Deleting excess data in a macro | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | New Users to Excel | |||
How do i copy columns of data in notepad into excel? | Excel Discussion (Misc queries) | |||
Daily Macro to Download Data, Order and paste in order | Excel Worksheet Functions |