Comparing two columns of Data

H

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,603
Office Version
  1. 2021
Platform
  1. Windows
I have account numbers in Column A as well in Column E from row 2 onwards. I would like a formula or VBA code that will compare the two rows of numbers and extract those that are unique

your assistance in this regard will be most appreciated
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
howard said:
I have account numbers in Column A as well in Column E from row 2 onwards. I would like a formula or VBA code that will compare the two rows of numbers and extract those that are unique

your assistance in this regard will be most appreciated
Click to expand...
Are the account "numbers" genuinely numbers, or do they have some alphabetical characters in them as well?

If numbers, what is their approximate magnitude?
 
Upvote 0
Or just try this one on some test data
Code: 
Sub compare_A_and_E()
Dim d1 As Object, d2 As Object
Dim lra As Long, lre As Long
Dim u(), v()
Dim ka As Long, ke As Long, c

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
lra = Range("A" & Rows.Count).End(3).Row - 1
lre = Range("E" & Rows.Count).End(3).Row - 1
ReDim u(1 To lra, 1 To 1), v(1 To lre, 1 To 1)

For Each c In Range("A2").Resize(lra).Value
    d1(c) = 1
Next

For Each c In Range("E2").Resize(lre).Value
    d2(c) = 1
    If Len(c) > 0 And d1(c) <> 1 Then
        ke = ke + 1
        v(ke, 1) = c
    End If
Next

For Each c In Range("A2").Resize(lra).Value
    If Len(c) > 0 And d2(c) <> 1 Then
        ka = ka + 1
        u(ka, 1) = c
    End If
Next

Range("B1") = "Unique in A"
Range("B2").Resize(ka) = u
Range("F1") = "Unique in B"
Range("F2").Resize(ke) = v

End Sub
 
Upvote 0
Hi Mirabeau

Thanks for the reply. The account numbers also contain alpha characters at the end-see sample data below

Regards

Howard


Book2
ABCDE
151391116
255001122
355011123
455031125
555041128
655391131
73000D2209
83001D2221
93002D3000
103004D3001
113006D3006D
123008D3008D
133009D3009D
143015D3100D
153100D3105D
163101D3106D
173102D3107D
Sheet1
 
Upvote 0
Hi Mirabeau

thanks for the code, much appreciated. It works perfectly. It would be appreciated if you could amend you code to create a third scenario in Col G to extract those Numbers that are unique to both Col A & E

Regards

Howard
 
Upvote 0
howard said:
Hi Mirabeau

thanks for the code, much appreciated. It works perfectly. It would be appreciated if you could amend you code to create a third scenario in Col G to extract those Numbers that are unique to both Col A & E

Regards

Howard
Click to expand...
Replace the bottom 4 lines of the code (above End Sub) by
Code: 
Range("G2").Resize(ka) = u
Range("G" & ka + 2).Resize(ke) = v
 
Upvote 0
Hi Mirabeau

Thanks for the help, much appreciated

Regards

Howard
 
Upvote 0
Hi Mirabeau,

Thanks a lot for your script, it seems to be able to do what I need. I've tried running it but ran into a 'Run-time error 429, ActiveX component can't create object' on the line "Set d1 = CreateObject("scripting.dictionary"). Any idea of how I can fix this?

Also, will this find the unique values in each column? For example, if I have two columns RANGE1 and RANGE2, and RANGE1 contains 1, 1, 4 and RANGE2 contains 1, 2, 3 then the script should return that the unique value in RANGE1 is 1, and the unique values in RANGE2 are 2, 3.

Thanks again!
 
Upvote 0
hejsanb said:
Hi Mirabeau,

Thanks a lot for your script, it seems to be able to do what I need. I've tried running it but ran into a 'Run-time error 429, ActiveX component can't create object' on the line "Set d1 = CreateObject("scripting.dictionary"). Any idea of how I can fix this?

Also, will this find the unique values in each column? For example, if I have two columns RANGE1 and RANGE2, and RANGE1 contains 1, 1, 4 and RANGE2 contains 1, 2, 3 then the script should return that the unique value in RANGE1 is 1, and the unique values in RANGE2 are 2, 3.

Thanks again!
Click to expand...
Hmm...

That's an unexpected hitch.

Here's some suggestions.

Can you create any other ActiveX objects with your hardware/software setup?

Is your syntax correct? That is, are you using the syntax exactly as I posted it?

Are you using a Mac?

What verion of Windows (if you're using Windows) and what version of Excel?

(I shouldn't really ask this, but just for completeness, were you using a legal copy of Excel?)

You can check the Microsoft website/knowledge base for more info on problems with creating activeX objects. Also usefully check Google on the topic.

You can use other methods of sorting out uniques, such as advanced filter, or alternative VBA codes.

If the data you need to check are just numbers, rather than text, then an easy and very fast workaround could be used.


Finally, you can try the following alternative code formulation and see if it does anything for you.
But follow these instructions before trying to run it.
1. Open the code module window. Say by pressing Alt+F11
2. At the menu on the top, click tools, then references
3. Scroll down to Microsoft Scripting Runtime, and check (or tick) that
4. Run the following code
Code: 
Sub compare_A_and_E()

Dim d1 As New dictionary, d2 As New dictionary
Dim lra As Long, lre As Long
Dim u(), v()
Dim ka As Long, ke As Long, c


d1.CompareMode = 1
d2.CompareMode = 1
lra = Range("A" & Rows.Count).End(3).Row - 1
lre = Range("E" & Rows.Count).End(3).Row - 1


ReDim u(1 To lra, 1 To 1), v(1 To lre, 1 To 1)


For Each c In Range("A2").Resize(lra).Value
    d1(c) = 1
Next
Range("B2").Resize(d1.Count) = Application.Transpose(d1.Keys)
Range("B1") = "Unique in A"


For Each c In Range("E2").Resize(lre).Value
    d1(c) = 1
    d2(c) = 1
Next
Range("F2").Resize(d2.Count) = Application.Transpose(d2.Keys)
Range("F1") = "Unique in B"


Range("H2").Resize(d1.Count) = Application.Transpose(d1.Keys)
Range("H1") = "Unique over both"


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Complex lookup formula for simple Data Validation
Replies
3
Views
150
Eric W
Eric W
M
  • Question Question
Combine Daily Sales and Prepare csv to import into Quickbooks Online for journal entries
Replies
1
Views
77
Joe4
Joe4
A
  • Solved
Index Match Function
Replies
3
Views
111
Fluff
Fluff
B
  • Question Question
Needing code help to sort data
Replies
2
Views
72
Bret1
B
S
  • Question Question
Comparing Columns of names
Replies
5
Views
340
Zot
Z

Forum statistics

Threads
1,225,301
Messages
6,184,148
Members
453,219
Latest member
Justinjc79

Share this page

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
Back
Top