Comparing two columns of Data

[howard]

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

 

[mirabeau]

Thank you very much for taking the time to write that out the more complicated way! I really appreciate it! It does the trick, with a few exceptions. My testing list looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, it is successfully disregarding the extra '1' in Column A, but for some reason it is printing out '4' twice. It's also returning the blank value in Column B. I wish I knew how to change the code to consider these exceptions! Hope you have the patience to help me with this

Thank you again!

Ok. I was skimping a bit on a couple of my ranges.

Try this modified version, which by my testing remedies the items you pointed out, and also a couple of perhaps less obvious ones.

Sub scode2()
Dim a, b
Dim ua(), ub()
Dim qa() As Boolean, qb() As Boolean
Dim rwa As Long, rwb As Long
Dim ka As Long, kb As Long
Dim i As Long, j As Long

rwa = Range("A" & Rows.Count).End(3).Row + 1
rwb = Range("B" & Rows.Count).End(3).Row + 1
ReDim ua(1 To rwa, 1 To 1), ub(1 To rwb, 1 To 1)
ReDim qa(rwa), qb(rwb)

a = Range("A1").Resize(rwa)
b = Range("B1").Resize(rwb)

For i = 1 To rwa
    If qa(i) = False Then
    For j = i + 1 To rwa
        If qa(j) = False Then
            If a(i, 1) = a(j, 1) Then qa(j) = True
        End If
    Next j
    End If
Next i

For i = 1 To rwb
    If qb(i) = False Then
    For j = i + 1 To rwb
        If qb(j) = False Then
            If b(i, 1) = b(j, 1) Then qb(j) = True
        End If
    Next j
    End If
Next i

For i = 1 To rwa
    If qa(i) = False Then
    For j = 1 To rwb
        If qb(j) = False Then
            If a(i, 1) = b(j, 1) Then
                qa(i) = True
                qb(j) = True
            End If
        End If
    Next j
    End If
Next i

For i = 1 To rwa
    If qa(i) = False Then ka = ka + 1: ua(ka, 1) = a(i, 1)
Next i

For j = 1 To rwb
    If qb(j) = False Then kb = kb + 1: ub(kb, 1) = b(j, 1)
Next j

If ka > 0 Then Range("D1").Resize(ka) = ua
If kb > 0 Then Range("E1").Resize(kb) = ub

End Sub

Biz,

I think that should fix the error you get as well.

 

[hejsanb]

Ok. I was skimping a bit on a couple of my ranges.

Try this modified version, which by my testing remedies the items you pointed out, and also a couple of perhaps less obvious ones.

...

Biz,

I think that should fix the error you get as well.

That works amazingly! Thank you so much! I have one (I hope) last problem... Both lists can contain alphanumeric values with different cases, and your script is differentiating by case. For example, if LIST1 contains "Alpha" and LIST2 contains "alpha", they are both showing up as uniques on each others lists. Is there any way to alter your script to make it disregard cases?

I would also like to add a counter to see how many unique values there are in each list. Do you think it would be easiest to do this with a COUNTIF function, or is there an easier way to build this into your script?

I can't thank you enough for your help with this!

 

[mirabeau]

That works amazingly! Thank you so much! I have one (I hope) last problem... Both lists can contain alphanumeric values with different cases, and your script is differentiating by case. For example, if LIST1 contains "Alpha" and LIST2 contains "alpha", they are both showing up as uniques on each others lists. Is there any way to alter your script to make it disregard cases?

Put

Option Compare Text

above the top of the code.

I would also like to add a counter to see how many unique values there are in each list. Do you think it would be easiest to do this with a COUNTIF function, or is there an easier way to build this into your script?

These counters are already built into the script as it is.

ka is the count of the unique values in columnA that don't appear in columnB, and conversely for kb.

You can locate these where you like on the worksheet, or even, at the end of the code, use something like.

MsgBox ka & " unique values in A that are not in B" & Chr(10) & _
        kb & " unique values in B that are not in A"

 

[Biz]

Ok. I was skimping a bit on a couple of my ranges.

Try this modified version, which by my testing remedies the items you pointed out, and also a couple of perhaps less obvious ones.

Sub scode2()
Dim a, b
Dim ua(), ub()
Dim qa() As Boolean, qb() As Boolean
Dim rwa As Long, rwb As Long
Dim ka As Long, kb As Long
Dim i As Long, j As Long

rwa = Range("A" & Rows.Count).End(3).Row + 1
rwb = Range("B" & Rows.Count).End(3).Row + 1
ReDim ua(1 To rwa, 1 To 1), ub(1 To rwb, 1 To 1)
ReDim qa(rwa), qb(rwb)

a = Range("A1").Resize(rwa)
b = Range("B1").Resize(rwb)

For i = 1 To rwa
    If qa(i) = False Then
    For j = i + 1 To rwa
        If qa(j) = False Then
            If a(i, 1) = a(j, 1) Then qa(j) = True
        End If
    Next j
    End If
Next i

For i = 1 To rwb
    If qb(i) = False Then
    For j = i + 1 To rwb
        If qb(j) = False Then
            If b(i, 1) = b(j, 1) Then qb(j) = True
        End If
    Next j
    End If
Next i

For i = 1 To rwa
    If qa(i) = False Then
    For j = 1 To rwb
        If qb(j) = False Then
            If a(i, 1) = b(j, 1) Then
                qa(i) = True
                qb(j) = True
            End If
        End If
    Next j
    End If
Next i

For i = 1 To rwa
    If qa(i) = False Then ka = ka + 1: ua(ka, 1) = a(i, 1)
Next i

For j = 1 To rwb
    If qb(j) = False Then kb = kb + 1: ub(kb, 1) = b(j, 1)
Next j

If ka > 0 Then Range("D1").Resize(ka) = ua
If kb > 0 Then Range("E1").Resize(kb) = ub

End Sub

Biz,

I think that should fix the error you get as well.

Kwel it works!

 

[hejsanb]

Put

Option Compare Text

above the top of the code.



These counters are already built into the script as it is.

ka is the count of the unique values in columnA that don't appear in columnB, and conversely for kb.

You can locate these where you like on the worksheet, or even, at the end of the code, use something like.

MsgBox ka & " unique values in A that are not in B" & Chr(10) & _
        kb & " unique values in B that are not in A"

Works great! Thanks!!!

 

[hejsanb]

Put

Option Compare Text

above the top of the code.



These counters are already built into the script as it is.

ka is the count of the unique values in columnA that don't appear in columnB, and conversely for kb.

You can locate these where you like on the worksheet, or even, at the end of the code, use something like.

MsgBox ka & " unique values in A that are not in B" & Chr(10) & _
        kb & " unique values in B that are not in A"

It seems that the script stops checking numeric values for uniques in each list after I put the

Option Compare Text

option in. Is there any way to make it check numbers as well as text (not case sensitive)?

 

[hejsanb]

It seems that the script stops checking numeric values for uniques in each list after I put the option in. Is there any way to make it check numbers as well as text (not case sensitive)?

My apologies, I have investigated further and am mistaken... It does in fact check numeric values.