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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,803
Office Version
  1. 2010
Platform
  1. Windows
try this code which uses scripting dictionaries which is usually the best way to remove duplicates with VBA:
VBA Code:
Sub test()
Dim dica As Object
Set dica = CreateObject("scripting.Dictionary")
Dim dicb As Object
Set dicb = CreateObject("scripting.Dictionary")


lasta = Cells(Rows.Count, "A").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lasta, 1))
For i = 1 To lasta
 If cola(i, 1) <> "" Then
 dica(cola(i, 1)) = 0
 End If
Next i

lastb = Cells(Rows.Count, "b").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lasta, 2))
If lasta > lastb Then
 maxrow = lasta
Else
 maxrow = lastb
End If
outarr = Range(Cells(1, 3), Cells(maxrow, 7))

indi = 2

For i = 1 To lastb
 If colb(i, 1) <> "" Then
 dicb(colb(i, 1)) = 0
  If dica.exists((colb(i, 1))) Then
    outarr(indi, 1) = colb(i, 1)
    outarr(indi, 2) = colb(i, 1)
    indi = indi + 1
  End If
 End If
Next i
indi = 1
For i = 1 To lastb
  If Not (dica.exists((colb(i, 1)))) Then
    outarr(indi, 4) = colb(i, 1)
    indi = indi + 1
  End If
Next i
indi = 1
For i = 1 To lasta
  If Not (dicb.exists((cola(i, 1)))) Then
    outarr(indi, 3) = cola(i, 1)
    indi = indi + 1
  End If
Next i



Range(Cells(1, 3), Cells(maxrow, 7)) = ""
Range(Cells(1, 3), Cells(maxrow, 7)) = outarr


End Sub
 

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Thanks but this still eliminates both "12" in column A when there is only one "12" in column B. I need it to only eliminate one "12" and still show a "12" outstanding
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,803
Office Version
  1. 2010
Platform
  1. Windows
Are you sure that both 12 in column A are identical? because it certainly doesn't in my test. Also it is very difficult to see how that can happen with the dictionary approach
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,803
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

try this modification to see if it is better:
VBA Code:
Sub test2()
Dim dica As Object
Set dica = CreateObject("scripting.Dictionary")
Dim dicb As Object
Set dicb = CreateObject("scripting.Dictionary")


lasta = Cells(Rows.Count, "A").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lasta, 1))
For i = 1 To lasta
 If cola(i, 1) <> "" Then
 dica(cola(i, 1)) = 0
 End If
Next i

lastb = Cells(Rows.Count, "b").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lasta, 2))
If lasta > lastb Then
 maxrow = lasta
Else
 maxrow = lastb
End If
outarr = Range(Cells(1, 3), Cells(maxrow, 7))


For i = 1 To lastb
 If colb(i, 1) <> "" Then
 dicb(colb(i, 1)) = 0
 End If
Next i
indi = 1
 
 For Each Key In dica
  If dicb.exists(Key) Then
    outarr(indi, 1) = Key
    outarr(indi, 2) = Key
    indi = indi + 1
  End If
Next
indi = 1

For i = 1 To lastb
  If Not (dica.exists((colb(i, 1)))) Then
    outarr(indi, 4) = colb(i, 1)
    indi = indi + 1
  End If
Next i
indi = 1
For i = 1 To lasta
  If Not (dicb.exists((cola(i, 1)))) Then
    outarr(indi, 3) = cola(i, 1)
    indi = indi + 1
  End If
Next i



Range(Cells(1, 3), Cells(maxrow, 7)) = ""
Range(Cells(1, 3), Cells(maxrow, 7)) = outarr


End Sub
 

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Still not solved

Here is my sheet Row A and B are the figures that I'm comparing
ABCDEF
1​
9.2027.609.205,422.00
2​
27.6036.5027.6027.6040.0074,900.00
3​
36.5045.3036.5036.50270.00143,413.00
4​
40.0056.2045.3045.30570.00
5​
45.3056.3056.2056.201,300.00
6​
56.2069.7056.3056.301,870.00
7​
56.3080.5069.7069.702,084.50
8​
69.7083.8080.5080.504,800.00
9​
80.5084.0083.8083.805,422.80
10​
83.80103.6084.0084.007,120.00
11​
84.00114.90103.60103.607,300.00
12​
103.60141.90114.90114.907,790.00
13​
114.90155.00141.90141.909,230.00
14​
141.90160.00155.00155.0010,280.00
15​
155.00170.00160.00160.0010,500.00
16​
160.00212.52170.00170.0012,140.00
17​
170.00227.50212.52212.5214,650.00
18​
212.52250.00227.50227.5015,900.00
19​
227.50257.50250.00250.0017,620.00
20​
250.00300.00257.50257.5020,000.00
21​
257.50315.20300.00300.0021,480.00
22​
270.00350.00315.20315.2021,956.00
23​
300.00352.00350.00350.0030,380.00
24​
315.20371.20352.00352.0030,430.00
25​
350.00662.00371.20371.2032,750.00
26​
352.00705.20662.00662.0037,665.00
27​
371.20726.80705.20705.2039,345.00
28​
570.00836.80726.80726.8039,360.00
29​
662.001,084.00836.80836.8040,660.00
30​
705.201,200.001,084.001,084.0044,230.00
31​
726.801,378.001,200.001,200.0044,350.00
32​
836.801,434.001,378.001,378.0044,447.00
33​
1,084.001,680.001,434.001,434.0047,060.00
34​
1,200.001,742.001,680.001,680.0048,910.00
35​
1,300.001,805.701,742.001,742.0049,150.00
36​
1,378.001,821.001,805.701,805.7052,910.00
37​
1,434.001,824.401,821.001,821.0054,240.00
38​
1,680.002,007.601,824.401,824.4056,040.00
39​
1,742.002,012.602,007.602,007.6056,160.00
40​
1,805.702,093.402,012.602,012.6056,300.00
41​
1,821.002,153.802,093.402,093.4061,970.00
42​
1,824.402,161.302,153.802,153.8066,610.00
43​
1,870.002,270.102,161.302,161.3068,330.00
44​
2,007.602,375.002,270.102,270.1069,550.00
45​
2,012.602,650.002,375.002,375.0072,240.00
46​
2,084.502,836.002,650.002,650.0074,800.00
47​
2,093.402,983.002,836.002,836.0081,100.00
48​
2,153.803,037.002,983.002,983.0085,670.00
49​
2,161.303,125.403,037.003,037.00113,730.00
50​
2,270.103,198.203,125.403,125.40133,970.00
51​
2,375.003,364.303,198.203,198.20140,000.00
52​
2,650.003,495.003,364.303,364.30140,000.00
53​
2,836.003,642.503,495.003,495.00142,500.00
54​
2,983.004,000.003,642.503,642.50144,150.00
55​
3,037.004,077.434,000.004,000.00150,000.00
56​
3,125.404,364.704,077.434,077.43160,390.00
57​
3,198.204,400.004,364.704,364.70190,460.00
58​
3,364.304,474.704,400.004,400.00391,800.00
59​
3,495.004,477.554,474.704,474.70
60​
3,642.504,500.004,477.554,477.55
61​
4,000.004,614.604,500.004,500.00
62​
4,077.434,708.604,614.604,614.60
63​
4,364.704,823.804,708.604,708.60
64​
4,400.005,000.004,823.804,823.80
65​
4,474.705,059.605,000.005,000.00
66​
4,477.555,115.005,059.605,059.60
67​
4,500.005,400.005,115.005,115.00
68​
4,500.005,422.005,400.005,400.00
69​
4,614.605,450.005,450.005,450.00
70​
4,708.605,669.505,669.505,669.50
71​
4,800.005,776.105,776.105,776.10
72​
4,823.805,883.205,883.205,883.20
73​
5,000.005,950.005,950.005,950.00
74​
5,059.606,474.406,474.406,474.40
75​
5,115.006,666.006,666.006,666.00
76​
5,400.007,750.007,750.007,750.00
77​
5,422.809,180.009,180.009,180.00
78​
5,450.009,400.009,400.009,400.00
79​
5,669.509,540.009,540.009,540.00
80​
5,776.109,567.309,567.309,567.30
81​
5,883.209,740.009,740.009,740.00
82​
5,950.0010,210.0010,210.0010,210.00
83​
6,474.4010,480.0010,480.0010,480.00
84​
6,666.0010,620.0010,620.0010,620.00
85​
7,120.0011,200.0011,200.0011,200.00
86​
7,300.0011,464.1011,464.1011,464.10
87​
7,750.0011,807.2011,807.2011,807.20
88​
7,790.0011,855.0011,855.0011,855.00
89​
9,180.0012,145.4012,145.4012,145.40
90​
9,230.0012,550.0012,550.0012,550.00
91​
9,400.0013,390.0013,390.0013,390.00
92​
9,540.0013,535.1013,535.1013,535.10
93​
9,567.3013,628.3013,628.3013,628.30
94​
9,740.0013,957.9013,957.9013,957.90
95​
10,210.0014,062.0014,062.0014,062.00
96​
10,280.0014,875.0014,875.0014,875.00
97​
10,480.0014,950.0014,950.0014,950.00
98​
10,500.0015,200.0015,200.0015,200.00
99​
10,620.0015,420.0015,420.0015,420.00
100​
11,200.0016,066.5016,066.5016,066.50
101​
11,464.1016,500.0016,500.0016,500.00
102​
11,807.2016,520.0016,520.0016,520.00
103​
11,855.0016,600.0016,600.0016,600.00
104​
12,140.0017,099.7017,099.7017,099.70
105​
12,145.4017,220.0017,220.0017,220.00
106​
12,550.0017,340.0017,340.0017,340.00
107​
13,390.0017,788.0017,788.0017,788.00
108​
13,535.1018,880.0018,880.0018,880.00
109​
13,628.3019,060.0019,060.0019,060.00
110​
13,957.9020,240.0020,240.0020,240.00
111​
14,062.0021,400.0021,400.0021,400.00
112​
14,650.0021,500.0021,500.0021,500.00
113​
14,875.0022,260.0022,260.0022,260.00
114​
14,950.0022,550.0022,550.0022,550.00
115​
15,200.0023,720.0023,720.0023,720.00
116​
15,420.0023,800.0023,800.0023,800.00
117​
15,900.0023,840.0023,840.0023,840.00
118​
16,066.5024,790.0024,790.0024,790.00
119​
16,500.0025,500.0025,500.0025,500.00
120​
16,520.0026,099.2026,099.2026,099.20
121​
16,600.0026,260.4026,260.4026,260.40
122​
17,099.7027,200.0027,200.0027,200.00
123​
17,220.0027,640.0027,640.0027,640.00
124​
17,340.0027,686.8027,686.8027,686.80
125​
17,620.0027,760.0027,760.0027,760.00
126​
17,788.0027,780.0027,780.0027,780.00
127​
18,880.0027,915.0027,915.0027,915.00
128​
19,060.0027,930.7027,930.7027,930.70
129​
20,000.0029,620.0029,620.0029,620.00
130​
20,240.0029,942.1029,942.1029,942.10
131​
21,400.0030,060.0030,060.0030,060.00
132​
21,480.0030,670.0030,670.0030,670.00
133​
21,500.0030,800.0030,800.0030,800.00
134​
21,956.0030,850.0030,850.0030,850.00
135​
22,260.0031,400.0031,400.0031,400.00
136​
22,550.0031,608.6031,608.6031,608.60
137​
23,720.0032,800.0032,800.0032,800.00
138​
23,800.0032,876.0032,876.0032,876.00
139​
23,840.0033,210.0033,210.0033,210.00
140​
24,790.0034,220.0034,220.0034,220.00
141​
25,500.0034,860.0034,860.0034,860.00
142​
26,099.2036,040.0036,040.0036,040.00
143​
26,260.4036,300.0036,300.0036,300.00
144​
27,200.0036,627.3036,627.3036,627.30
145​
27,640.0038,240.0038,240.0038,240.00
146​
27,686.8038,460.8538,460.8538,460.85
147​
27,760.0039,390.0039,390.0039,390.00
148​
27,780.0040,441.5040,441.5040,441.50
149​
27,915.0042,000.0042,000.0042,000.00
150​
27,930.7042,950.0042,950.0042,950.00
151​
29,620.0044,430.0044,430.0044,430.00
152​
29,620.0044,490.0044,490.0044,490.00
153​
29,942.1045,430.0045,430.0045,430.00
154​
30,060.0045,938.0045,938.0045,938.00
155​
30,380.0047,794.2047,794.2047,794.20
156​
30,430.0048,660.0048,660.0048,660.00
157​
30,670.0049,710.0049,710.0049,710.00
158​
30,800.0050,000.0050,000.0050,000.00
159​
30,850.0051,820.0051,820.0051,820.00
160​
31,400.0052,090.0052,090.0052,090.00
161​
31,608.6052,340.0052,340.0052,340.00
162​
32,750.0052,510.0052,510.0052,510.00
163​
32,800.0052,700.0052,700.0052,700.00
164​
32,876.0054,700.0054,700.0054,700.00
165​
33,210.0056,110.0056,110.0056,110.00
166​
34,220.0058,380.0058,380.0058,380.00
167​
34,860.0059,890.0059,890.0059,890.00
168​
36,040.0061,370.0061,370.0061,370.00
169​
36,300.0065,160.0065,160.0065,160.00
170​
36,627.3065,918.7065,918.7065,918.70
171​
37,665.0067,100.0067,100.0067,100.00
172​
38,240.0067,390.0067,390.0067,390.00
173​
38,460.8569,003.1069,003.1069,003.10
174​
39,345.0069,990.0069,990.0069,990.00
175​
39,360.0074,900.0076,950.0076,950.00
176​
39,390.0076,950.0081,040.0081,040.00
177​
40,441.5081,040.0082,230.0082,230.00
178​
40,660.0082,230.0082,550.0082,550.00
179​
42,000.0082,550.0083,490.0083,490.00
180​
42,950.0083,490.0083,500.0083,500.00
181​
44,230.0083,500.0083,650.0083,650.00
182​
44,350.0083,650.0083,790.0083,790.00
183​
44,430.0083,790.0088,780.0088,780.00
184​
44,447.0088,780.0093,350.0093,350.00
185​
44,490.0093,350.0095,150.0095,150.00
186​
45,430.0095,150.0095,470.0095,470.00
187​
45,938.0095,470.0098,521.1098,521.10
188​
47,060.0098,521.1099,970.0099,970.00
189​
47,794.2099,970.00101,120.00101,120.00
190​
48,660.00101,120.00105,324.80105,324.80
191​
48,910.00105,324.80108,170.00108,170.00
192​
49,150.00108,170.00108,380.00108,380.00
193​
49,710.00108,380.00109,530.00109,530.00
194​
50,000.00109,530.00110,080.00110,080.00
195​
51,820.00110,080.00115,890.00115,890.00
196​
52,090.00115,890.00124,960.00124,960.00
197​
52,340.00124,960.00130,170.00130,170.00
198​
52,510.00130,170.00135,770.00135,770.00
199​
52,700.00135,770.00146,060.00146,060.00
200​
52,910.00143,413.00150,220.00150,220.00
201​
54,240.00146,060.00153,980.00153,980.00
202​
54,700.00150,220.00158,560.40158,560.40
203​
56,040.00153,980.00162,990.00162,990.00
204​
56,110.00158,560.40165,020.00165,020.00
205​
56,160.00162,990.00205,100.00205,100.00
206​
56,300.00165,020.00230,450.00230,450.00
207​
58,380.00205,100.00258,820.00258,820.00
208​
59,890.00230,450.00308,750.00308,750.00
209​
61,370.00258,820.00315,980.00315,980.00
210​
61,970.00308,750.00
211​
65,160.00315,980.00
212​
65,918.70
213​
66,610.00
214​
67,100.00
215​
67,390.00
216​
68,330.00
217​
69,003.10
218​
69,550.00
219​
69,990.00
220​
72,240.00
221​
74,800.00
222​
76,950.00
223​
81,040.00
224​
81,100.00
225​
82,230.00
226​
82,550.00
227​
83,490.00
228​
83,500.00
229​
83,650.00
230​
83,790.00
231​
85,670.00
232​
88,780.00
233​
93,350.00
234​
95,150.00
235​
95,470.00
236​
98,521.10
237​
99,970.00
238​
101,120.00
239​
105,324.80
240​
108,170.00
241​
108,380.00
242​
109,530.00
243​
110,080.00
244​
113,730.00
245​
115,890.00
246​
124,960.00
247​
130,170.00
248​
133,970.00
249​
135,770.00
250​
140,000.00
251​
140,000.00
252​
142,500.00
253​
144,150.00
254​
146,060.00
255​
150,000.00
256​
150,220.00
257​
153,980.00
258​
158,560.40
259​
160,390.00
260​
162,990.00
261​
165,020.00
262​
190,460.00
263​
205,100.00
264​
230,450.00
265​
258,820.00
266​
308,750.00
267​
315,980.00
268​
391,800.00

Column A line 67+68 has 4,500.00 in it
Column B line 60 has 4,500.00 in it

The results according to Column C61+D61 shows 4,500.00 but Column E does not show the 4,500.00 as this is the one not matched.

Same goes for
Column A line 151+152 has 29,620.00 in it
Column B line 129 has 29,620.00 in it

The results according to Column C129+D129 shows 29,620.00 but Column E does not show the 29,620.00 as this is the one not matched.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,803
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Forget my solution I misunderstood your problem, I didn't realise you wanted to count the number of matches of the same number and display them in col E orF is the count was different. Very sorry
 

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Forget my solution I misunderstood your problem, I didn't realise you wanted to count the number of matches of the same number and display them in col E orF is the count was different. Very sorry
No problem for a moment I thought I was doing something wrong
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,803
Office Version
  1. 2010
Platform
  1. Windows
try this solution which doesn't use dictionaries:
VBA Code:
Sub test2()
lasta = Cells(Rows.Count, "A").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lasta, 1))

lastb = Cells(Rows.Count, "b").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lastb, 2))
If lasta > lastb Then
 maxrow = lasta
Else
 maxrow = lastb
End If
outarr = Range(Cells(1, 3), Cells(maxrow, 7))
indi = 1
For i = 1 To lasta
   For j = 1 To lastb
   If cola(i, 1) <> "" Then
    If cola(i, 1) = colb(j, 1) Then
     outarr(indi, 1) = cola(i, 1)
     outarr(indi, 2) = colb(j, 1)
     cola(i, 1) = ""
     colb(j, 1) = ""
     indi = indi + 1
     Exit For
    End If
   End If
   Next j
Next i
indi = 1
For i = 1 To lasta
    If cola(i, 1) <> "" Then
     outarr(indi, 3) = cola(i, 1)
     indi = indi + 1
    End If
   Next i
indi = 1
For i = 1 To lastb
    If colb(i, 1) <> "" Then
     outarr(indi, 4) = colb(i, 1)
     indi = indi + 1
    End If
Next i

Range(Cells(1, 3), Cells(maxrow, 7)) = ""
Range(Cells(1, 3), Cells(maxrow, 7)) = outarr


End Sub
 
Solution

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
87
Office Version
  1. 365
Platform
  1. Windows
You are a star it definitely looks like it is working. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

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