Compare Columns VBA

[Brentsa]

Over this weekend I realized a error in the VBA result

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
	A	B
Book1
1
2	1	2
3	9	3
4	8	4
5	10	6
6	one	8
7	3	one
8	two	four
9	12	9
10	7	12
11	4	15
12	13	1
13	11	5
14	22	8
15	12
16	8

So the results should look like this:

Sheet2
	A	B	C	D	E
Book1
1
2	1	1		7	2
3	3	3		10	5
4	4	4		11	6
5	8	8		12	15
6	8	8		13
7	9	9		22
8	12	12

 

[offthelip]

try this code which uses scripting dictionaries which is usually the best way to remove duplicates with VBA:

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]

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]

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]

try this modification to see if it is better:

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]

Still not solved

Here is my sheet Row A and B are the figures that I'm comparing

	A	B	C	D	E	F
1​	9.20	27.60			9.20	5,422.00
2​	27.60	36.50	27.60	27.60	40.00	74,900.00
3​	36.50	45.30	36.50	36.50	270.00	143,413.00
4​	40.00	56.20	45.30	45.30	570.00
5​	45.30	56.30	56.20	56.20	1,300.00
6​	56.20	69.70	56.30	56.30	1,870.00
7​	56.30	80.50	69.70	69.70	2,084.50
8​	69.70	83.80	80.50	80.50	4,800.00
9​	80.50	84.00	83.80	83.80	5,422.80
10​	83.80	103.60	84.00	84.00	7,120.00
11​	84.00	114.90	103.60	103.60	7,300.00
12​	103.60	141.90	114.90	114.90	7,790.00
13​	114.90	155.00	141.90	141.90	9,230.00
14​	141.90	160.00	155.00	155.00	10,280.00
15​	155.00	170.00	160.00	160.00	10,500.00
16​	160.00	212.52	170.00	170.00	12,140.00
17​	170.00	227.50	212.52	212.52	14,650.00
18​	212.52	250.00	227.50	227.50	15,900.00
19​	227.50	257.50	250.00	250.00	17,620.00
20​	250.00	300.00	257.50	257.50	20,000.00
21​	257.50	315.20	300.00	300.00	21,480.00
22​	270.00	350.00	315.20	315.20	21,956.00
23​	300.00	352.00	350.00	350.00	30,380.00
24​	315.20	371.20	352.00	352.00	30,430.00
25​	350.00	662.00	371.20	371.20	32,750.00
26​	352.00	705.20	662.00	662.00	37,665.00
27​	371.20	726.80	705.20	705.20	39,345.00
28​	570.00	836.80	726.80	726.80	39,360.00
29​	662.00	1,084.00	836.80	836.80	40,660.00
30​	705.20	1,200.00	1,084.00	1,084.00	44,230.00
31​	726.80	1,378.00	1,200.00	1,200.00	44,350.00
32​	836.80	1,434.00	1,378.00	1,378.00	44,447.00
33​	1,084.00	1,680.00	1,434.00	1,434.00	47,060.00
34​	1,200.00	1,742.00	1,680.00	1,680.00	48,910.00
35​	1,300.00	1,805.70	1,742.00	1,742.00	49,150.00
36​	1,378.00	1,821.00	1,805.70	1,805.70	52,910.00
37​	1,434.00	1,824.40	1,821.00	1,821.00	54,240.00
38​	1,680.00	2,007.60	1,824.40	1,824.40	56,040.00
39​	1,742.00	2,012.60	2,007.60	2,007.60	56,160.00
40​	1,805.70	2,093.40	2,012.60	2,012.60	56,300.00
41​	1,821.00	2,153.80	2,093.40	2,093.40	61,970.00
42​	1,824.40	2,161.30	2,153.80	2,153.80	66,610.00
43​	1,870.00	2,270.10	2,161.30	2,161.30	68,330.00
44​	2,007.60	2,375.00	2,270.10	2,270.10	69,550.00
45​	2,012.60	2,650.00	2,375.00	2,375.00	72,240.00
46​	2,084.50	2,836.00	2,650.00	2,650.00	74,800.00
47​	2,093.40	2,983.00	2,836.00	2,836.00	81,100.00
48​	2,153.80	3,037.00	2,983.00	2,983.00	85,670.00
49​	2,161.30	3,125.40	3,037.00	3,037.00	113,730.00
50​	2,270.10	3,198.20	3,125.40	3,125.40	133,970.00
51​	2,375.00	3,364.30	3,198.20	3,198.20	140,000.00
52​	2,650.00	3,495.00	3,364.30	3,364.30	140,000.00
53​	2,836.00	3,642.50	3,495.00	3,495.00	142,500.00
54​	2,983.00	4,000.00	3,642.50	3,642.50	144,150.00
55​	3,037.00	4,077.43	4,000.00	4,000.00	150,000.00
56​	3,125.40	4,364.70	4,077.43	4,077.43	160,390.00
57​	3,198.20	4,400.00	4,364.70	4,364.70	190,460.00
58​	3,364.30	4,474.70	4,400.00	4,400.00	391,800.00
59​	3,495.00	4,477.55	4,474.70	4,474.70
60​	3,642.50	4,500.00	4,477.55	4,477.55
61​	4,000.00	4,614.60	4,500.00	4,500.00
62​	4,077.43	4,708.60	4,614.60	4,614.60
63​	4,364.70	4,823.80	4,708.60	4,708.60
64​	4,400.00	5,000.00	4,823.80	4,823.80
65​	4,474.70	5,059.60	5,000.00	5,000.00
66​	4,477.55	5,115.00	5,059.60	5,059.60
67​	4,500.00	5,400.00	5,115.00	5,115.00
68​	4,500.00	5,422.00	5,400.00	5,400.00
69​	4,614.60	5,450.00	5,450.00	5,450.00
70​	4,708.60	5,669.50	5,669.50	5,669.50
71​	4,800.00	5,776.10	5,776.10	5,776.10
72​	4,823.80	5,883.20	5,883.20	5,883.20
73​	5,000.00	5,950.00	5,950.00	5,950.00
74​	5,059.60	6,474.40	6,474.40	6,474.40
75​	5,115.00	6,666.00	6,666.00	6,666.00
76​	5,400.00	7,750.00	7,750.00	7,750.00
77​	5,422.80	9,180.00	9,180.00	9,180.00
78​	5,450.00	9,400.00	9,400.00	9,400.00
79​	5,669.50	9,540.00	9,540.00	9,540.00
80​	5,776.10	9,567.30	9,567.30	9,567.30
81​	5,883.20	9,740.00	9,740.00	9,740.00
82​	5,950.00	10,210.00	10,210.00	10,210.00
83​	6,474.40	10,480.00	10,480.00	10,480.00
84​	6,666.00	10,620.00	10,620.00	10,620.00
85​	7,120.00	11,200.00	11,200.00	11,200.00
86​	7,300.00	11,464.10	11,464.10	11,464.10
87​	7,750.00	11,807.20	11,807.20	11,807.20
88​	7,790.00	11,855.00	11,855.00	11,855.00
89​	9,180.00	12,145.40	12,145.40	12,145.40
90​	9,230.00	12,550.00	12,550.00	12,550.00
91​	9,400.00	13,390.00	13,390.00	13,390.00
92​	9,540.00	13,535.10	13,535.10	13,535.10
93​	9,567.30	13,628.30	13,628.30	13,628.30
94​	9,740.00	13,957.90	13,957.90	13,957.90
95​	10,210.00	14,062.00	14,062.00	14,062.00
96​	10,280.00	14,875.00	14,875.00	14,875.00
97​	10,480.00	14,950.00	14,950.00	14,950.00
98​	10,500.00	15,200.00	15,200.00	15,200.00
99​	10,620.00	15,420.00	15,420.00	15,420.00
100​	11,200.00	16,066.50	16,066.50	16,066.50
101​	11,464.10	16,500.00	16,500.00	16,500.00
102​	11,807.20	16,520.00	16,520.00	16,520.00
103​	11,855.00	16,600.00	16,600.00	16,600.00
104​	12,140.00	17,099.70	17,099.70	17,099.70
105​	12,145.40	17,220.00	17,220.00	17,220.00
106​	12,550.00	17,340.00	17,340.00	17,340.00
107​	13,390.00	17,788.00	17,788.00	17,788.00
108​	13,535.10	18,880.00	18,880.00	18,880.00
109​	13,628.30	19,060.00	19,060.00	19,060.00
110​	13,957.90	20,240.00	20,240.00	20,240.00
111​	14,062.00	21,400.00	21,400.00	21,400.00
112​	14,650.00	21,500.00	21,500.00	21,500.00
113​	14,875.00	22,260.00	22,260.00	22,260.00
114​	14,950.00	22,550.00	22,550.00	22,550.00
115​	15,200.00	23,720.00	23,720.00	23,720.00
116​	15,420.00	23,800.00	23,800.00	23,800.00
117​	15,900.00	23,840.00	23,840.00	23,840.00
118​	16,066.50	24,790.00	24,790.00	24,790.00
119​	16,500.00	25,500.00	25,500.00	25,500.00
120​	16,520.00	26,099.20	26,099.20	26,099.20
121​	16,600.00	26,260.40	26,260.40	26,260.40
122​	17,099.70	27,200.00	27,200.00	27,200.00
123​	17,220.00	27,640.00	27,640.00	27,640.00
124​	17,340.00	27,686.80	27,686.80	27,686.80
125​	17,620.00	27,760.00	27,760.00	27,760.00
126​	17,788.00	27,780.00	27,780.00	27,780.00
127​	18,880.00	27,915.00	27,915.00	27,915.00
128​	19,060.00	27,930.70	27,930.70	27,930.70
129​	20,000.00	29,620.00	29,620.00	29,620.00
130​	20,240.00	29,942.10	29,942.10	29,942.10
131​	21,400.00	30,060.00	30,060.00	30,060.00
132​	21,480.00	30,670.00	30,670.00	30,670.00
133​	21,500.00	30,800.00	30,800.00	30,800.00
134​	21,956.00	30,850.00	30,850.00	30,850.00
135​	22,260.00	31,400.00	31,400.00	31,400.00
136​	22,550.00	31,608.60	31,608.60	31,608.60
137​	23,720.00	32,800.00	32,800.00	32,800.00
138​	23,800.00	32,876.00	32,876.00	32,876.00
139​	23,840.00	33,210.00	33,210.00	33,210.00
140​	24,790.00	34,220.00	34,220.00	34,220.00
141​	25,500.00	34,860.00	34,860.00	34,860.00
142​	26,099.20	36,040.00	36,040.00	36,040.00
143​	26,260.40	36,300.00	36,300.00	36,300.00
144​	27,200.00	36,627.30	36,627.30	36,627.30
145​	27,640.00	38,240.00	38,240.00	38,240.00
146​	27,686.80	38,460.85	38,460.85	38,460.85
147​	27,760.00	39,390.00	39,390.00	39,390.00
148​	27,780.00	40,441.50	40,441.50	40,441.50
149​	27,915.00	42,000.00	42,000.00	42,000.00
150​	27,930.70	42,950.00	42,950.00	42,950.00
151​	29,620.00	44,430.00	44,430.00	44,430.00
152​	29,620.00	44,490.00	44,490.00	44,490.00
153​	29,942.10	45,430.00	45,430.00	45,430.00
154​	30,060.00	45,938.00	45,938.00	45,938.00
155​	30,380.00	47,794.20	47,794.20	47,794.20
156​	30,430.00	48,660.00	48,660.00	48,660.00
157​	30,670.00	49,710.00	49,710.00	49,710.00
158​	30,800.00	50,000.00	50,000.00	50,000.00
159​	30,850.00	51,820.00	51,820.00	51,820.00
160​	31,400.00	52,090.00	52,090.00	52,090.00
161​	31,608.60	52,340.00	52,340.00	52,340.00
162​	32,750.00	52,510.00	52,510.00	52,510.00
163​	32,800.00	52,700.00	52,700.00	52,700.00
164​	32,876.00	54,700.00	54,700.00	54,700.00
165​	33,210.00	56,110.00	56,110.00	56,110.00
166​	34,220.00	58,380.00	58,380.00	58,380.00
167​	34,860.00	59,890.00	59,890.00	59,890.00
168​	36,040.00	61,370.00	61,370.00	61,370.00
169​	36,300.00	65,160.00	65,160.00	65,160.00
170​	36,627.30	65,918.70	65,918.70	65,918.70
171​	37,665.00	67,100.00	67,100.00	67,100.00
172​	38,240.00	67,390.00	67,390.00	67,390.00
173​	38,460.85	69,003.10	69,003.10	69,003.10
174​	39,345.00	69,990.00	69,990.00	69,990.00
175​	39,360.00	74,900.00	76,950.00	76,950.00
176​	39,390.00	76,950.00	81,040.00	81,040.00
177​	40,441.50	81,040.00	82,230.00	82,230.00
178​	40,660.00	82,230.00	82,550.00	82,550.00
179​	42,000.00	82,550.00	83,490.00	83,490.00
180​	42,950.00	83,490.00	83,500.00	83,500.00
181​	44,230.00	83,500.00	83,650.00	83,650.00
182​	44,350.00	83,650.00	83,790.00	83,790.00
183​	44,430.00	83,790.00	88,780.00	88,780.00
184​	44,447.00	88,780.00	93,350.00	93,350.00
185​	44,490.00	93,350.00	95,150.00	95,150.00
186​	45,430.00	95,150.00	95,470.00	95,470.00
187​	45,938.00	95,470.00	98,521.10	98,521.10
188​	47,060.00	98,521.10	99,970.00	99,970.00
189​	47,794.20	99,970.00	101,120.00	101,120.00
190​	48,660.00	101,120.00	105,324.80	105,324.80
191​	48,910.00	105,324.80	108,170.00	108,170.00
192​	49,150.00	108,170.00	108,380.00	108,380.00
193​	49,710.00	108,380.00	109,530.00	109,530.00
194​	50,000.00	109,530.00	110,080.00	110,080.00
195​	51,820.00	110,080.00	115,890.00	115,890.00
196​	52,090.00	115,890.00	124,960.00	124,960.00
197​	52,340.00	124,960.00	130,170.00	130,170.00
198​	52,510.00	130,170.00	135,770.00	135,770.00
199​	52,700.00	135,770.00	146,060.00	146,060.00
200​	52,910.00	143,413.00	150,220.00	150,220.00
201​	54,240.00	146,060.00	153,980.00	153,980.00
202​	54,700.00	150,220.00	158,560.40	158,560.40
203​	56,040.00	153,980.00	162,990.00	162,990.00
204​	56,110.00	158,560.40	165,020.00	165,020.00
205​	56,160.00	162,990.00	205,100.00	205,100.00
206​	56,300.00	165,020.00	230,450.00	230,450.00
207​	58,380.00	205,100.00	258,820.00	258,820.00
208​	59,890.00	230,450.00	308,750.00	308,750.00
209​	61,370.00	258,820.00	315,980.00	315,980.00
210​	61,970.00	308,750.00
211​	65,160.00	315,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]

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]

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]

try this solution which doesn't use dictionaries:

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

 

[Brentsa]

You are a star it definitely looks like it is working.