Compare Columns VBA

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Over this weekend I realized a error in the VBA result

VBA Code:
VBA Code:
Sub CompareData()
Sheets("Calculator").Activate
Columns("A:G").Select
Selection.Style = "Comma"

'Compare Column A & B
Dim lrA As Long, lrB As Long

lrA = Range("A" & Rows.Count).End(xlUp).Row
lrB = Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("C1").Formula2 = Replace(Replace("=SORT(FILTER(A1:A@,ISNUMBER(MATCH(A1:A@,B1:B#,0)),""""))", "@", lrA), "#", lrB)
Range("D1").Formula2 = Replace(Replace("=SORT(FILTER(A1:A@,ISNUMBER(MATCH(A1:A@,B1:B#,0)),""""))", "@", lrA), "#", lrB)
Range("F1").Formula2 = Replace(Replace("=SORT(FILTER(A1:A@,ISNA(MATCH(A1:A@,B1:B#,0)),""""))", "@", lrA), "#", lrB)
Range("G1").Formula2 = Replace(Replace("=SORT(FILTER(B1:B#,ISNA(MATCH(B1:B#,A1:A@,0)),""""))", "@", lrA), "#", lrB)
With Intersect(ActiveSheet.UsedRange, Columns("C:G"))
.Value = .Value
End With
Columns("A:B").Delete
Application.ScreenUpdating = True
'Sort all Columns in ascending number format
Sheets("Calculator").Range("A:G", Range("A:G").End(xlDown)).Sort Key1:=Range("A:G"), Order1:=xlAscending, Header:=xlNo
Columns("A:G").Select
Selection.Style = "Comma"
Range("a1").Select

End Sub

In Column A is have duplicate numbers and Column B I have only one matching number. At the moment the VBA code eliminates both numbers in Column A as it has found corresponding number in Column B. What I need is for it to only elimate one set matching number and move the other non matching number to column. For example Column A has 8 and 12 in it twice. Column B has 8 twice but 12 once.

Sheet1
AB
Book1
1
212
393
484
5106
6one8
73one
8twofour
9129
10712
11415
12131
13115
14228
1512
168


So the results should look like this:
Sheet2
ABCDE
Book1
1
21172
333105
444116
5881215
68813
79922
81212
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
No trouble ( once I started solving the correct problem!!)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,583
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top