Comparing data in two columns in excel VBA

[starlight849]

Hi, I would like to compare values in two columns in excel 2007 vba.

Basically if a cell value doesn't exist in column B that exists in column A then add the value to the end of column B.

Quite new to excel vba. Would appreciate any help.

Thanks

[NeoPa]

You need to process trhough column A and checking each value against column B (using Find()). Any items not found need to be added by going to the cell below the bottom of column B, then using the code for Ctrl-UpArrow (which is End(xlUp)) to get the last cell used in ccolumn B, then going down one cell for the first unused cell below column B. This is where the value from the cell from column A is placed.

The code below illustrates one way this could be handled :

Option Explicit
 
Public Sub FixB()
    Dim lngFrom As Long, lngTo As Long
    Dim strVal As String
    Dim ranA As Range, ranB As Range
 
    lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2))
    lngTo = Val(Split(ActiveSheet.UsedRange.Address, "$")(4))
    For Each ranA In Range("A" & lngFrom & ":A" & lngTo)
        strVal = ranA
        If strVal > "" Then
            With Range("B" & lngFrom & ":B" & lngTo)
                If .Find(What:=strVal, _
                         LookIn:=xlValues, _
                         LookAt:=xlWhole) Is Nothing Then _
                    Range("B" & lngTo + 1).End(xlUp).Range("A2") = strVal
            End With
        End If
    Next ranA
End Sub

[starlight849]

Thanks so much for the quick, detailed response. I tried out your code and instead of only appending cells that contain differences, the entire range A is being appended to the end or range B. Is there a way that we can make only values in the cell in range A, that are not in range B, append to range B?

Thanks again.

[NeoPa]

That's hard to say. It works perfectly for me. Can you give an indication of the data you're working with? Maybe even attaching your test spreadsheet?

[starlight849]

It is a range of numeric characters with a starting alpha character, such as a12345. I'm not actually comparing column A and B, it is specifically column A and M. I assumed I changed the correct variables in the code, but maybe not. I can't attach the spreasheet, unfortunately. I do appreciate all your help.

[kadghar]

I think the fastest way to do it, even if the algorithm isn't so clean, is by reading once the values into arrays, then working with the arrays and write into excel only once one string array with the values...

Something like this:

Sub Columns()
Dim a
Dim b
a = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
b = Range(Cells(1, 2), Cells(1, 2).End(xlDown))
Dim c() As String
Dim n As Integer
For i = 1 To UBound(a)
    Dim boo1 As Boolean
    boo1 = False
    For j = 1 To UBound(b)
        If a(i, 1) = b(j, 1) Then
            boo1 = True
            Exit For
        End If
    Next
    If Not boo1 Then
        n = n + 1
        ReDim Preserve c(1 To n)
        c(n) = a(i, 1)
    End If
Next
Dim d() As String
ReDim d(1 To n, 1 To 1)
For i = 1 To n
    d(i, 1) = c(i)
Next
Range(Cells(UBound(b) + 1, 2), Cells(UBound(b) + n, 2)) = d
End Sub

HTH

[starlight849]

I think I found the problem. I believe it was due to a different case in one of the columns. Thanks for all your help.

[kadghar]

then in line 12 just change cases for comparing:

        If ucase(a(i, 1)) = ucase(b(j, 1)) Then
            boo1 = True
            Exit For
        End If

[NeoPa]

Starlight849:
I'm not actually comparing column A and B, it is specifically column A and M. I assumed I changed the correct variables in the code, but maybe not.

I cannot possibly comment on that, as you didn't include the code you tried.

To save time I'll create an amended version for you :

Option Explicit
 
Public Sub FixM()
    Dim lngFrom As Long, lngTo As Long
    Dim strVal As String
    Dim ranA As Range
 
    lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2))
    lngTo = Val(Split(ActiveSheet.UsedRange.Address, "$")(4))
    For Each ranA In Range("A" & lngFrom & ":A" & lngTo)
        strVal = ranA
        If strVal > "" Then
            With Range("M" & lngFrom & ":M" & lngTo)
                If .Find(What:=strVal, _
                         LookIn:=xlValues, _
                         LookAt:=xlWhole) Is Nothing Then _
                    Range("M" & lngTo + 1).End(xlUp).Range("A2") = strVal
            End With
        End If
    Next ranA
End Sub

It turns out that ranB wasn't required anyway, so that's gone too.

Next time, it would be a better idea to explain your situation accurately rather than metaphorically. I can't imagine why understanding a problem which involves column M would be any harder than one involving column B.

[starlight849]

Kadghar,
Thanks for your response. I am trying your approach now. If I was to use column M instead of B how would I change this line to reflect that? b = Range(Cells(1, 2), Cells(1, 2).End(xlDown))

[kadghar]

b is 2
m is 13 (hope so)

so just
b= range(cells(1,13), cells(1,13).end(xldown))
and when you write down "d", just do the same.

Remember that cells(i,j) i is for rows, j is for columns (weird, i know)

[Mihail]

You can customize how Excel show column's names.
Use the Office button (Top left side - colored as a rainbow).
This will help you to easy write VBA code.

Returning to your question, this code will do your job.
The single requirement is to have NOT empty cells in rows you are compare (A, B).

Sub AddToB()
Dim cA As Long
    cA = 1 ' column A
Dim cB As Long
    cB = 2 'column B
Dim rA As Long, rB As Long
 
    rA = 1 'First row in A column (if you have a column header,: rA=2)
    Do Until IsEmpty(Cells(rA, cA))
        rB = 1 'First row in B column (if you have a column header,: rB=2)
        Do Until IsEmpty(Cells(rB, cB))
            If Cells(rB, cB) = Cells(rA, cA) Then
                GoTo Next_rA
            End If
            rB = rB + 1
        Loop
        ' No cell in rB = Ra
        Cells(rB, cB) = Cells(rA, cA)
Next_rA:
        rA = rA + 1
    Loop
End Sub

[NeoPa]

@Starlight849
It would be nice if you could confirm that the code in post #9 does actually work and do the job perfectly. I suspect you may have missed it due to posting yourself near to that time. It's always advisable to check all posts since the last one you recognise to avoid such things, but that may well not have occurred to you yet as you're quite new to this I know.

Kadghar:
I think the fastest way to do it, ...

I think if you check this out you'll find very few, if any, scenarios where your approach will outperform using the built-in Find() method. It's not absolutely impossible, but as the number of cells to check increases, so does the interpretation overhead of all the lines of code.

Certainly, well managed array processing can perform faster than referencing object collections, but ReDimming is also (proportionally) a very large overhead indeed. I certainly wouldn't expect your claim of faster processing to be borne out by testing.

Alternatives are always welcome, of course, and we mostly think and work differently from others doing a similar job, so different approaches don't ever (in themselves) indicate any is right or wrong.

[starlight849]

NeoPa, your approach did indeed work. When I created a new excel workbook and used some sample data it worked fabulously. Perhaps the workbook I was working in has become corrupt. I will copy the data over and follow your approach.

Thanks for all of your help.

[NeoPa]

I'm pleased to have helped :-) A little intrigued that it didn't with your other data, but I understand your data is sensitive so we won't pursue that angle.

[kadghar]

@NeoPa

You're absolutely right, redimming will slow it down quite a bunch. But as I said before: even if the core algorithm is bad. If you reduce your interaction with excel, the code will be a few hudred times faster (literaly).

I've worked with codes that would take hours to run with excel functions inside a loop. And as soon as I replaced them with any lousy/lazy algorithm that reads/writes only once. The time is reduced to seconds.

[NeoPa]

Kadghar:
I've worked with codes that would take hours to run with excel functions inside a loop. And as soon as I replaced them with any lousy/lazy algorithm that reads/writes only once. The time is reduced to seconds.

I can imagine scenarios where this could be possible, but they're not common. I assume you turned off ScreenUpdating and Calculation was set to Manual while these long processes were running. Otherwise you would certainly expect some very long delays.

I can also imagine scenarios where the process would certainly be improved by taking it away from Excel cell storage and manipulating it in local memory (Implementing a specific type of Sort routine for instance - specifically not the inbuilt Sort() method). Using Find() in the code is very similar to this though. The performance of inbuilt functions is already heavily optimised. I think there may be a possibility of finding a scenario where that could be improved upon if the amount of data being processed were nearing Excel limits, but even that I'd need to check to be sure.

For a job like this, unless specified otherwise, tidy, sensible and efficient code is more usable and better for most projects than optimised code that doesn't share those other characteristics.

[starlight849]

Neopa,
I believe I figured out what the possible issue could have been with my original workbook. I had previously saved it as a .xls file and then converted it to a .xlsm file. Maybe the conversion caused the issue of the macros not working correctly.

Thanks again for your help.

Ps: If I was wanting to ignore the first row in the excel sheet, how would I go about that?

[NeoPa]

starlight849:
If I was wanting to ignore the first row in the excel sheet, how would I go about that?

You would change line #8 to :

lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2)) + 1

This tells it to ignore the first row of data that is found in the worksheet and start on the line immediately below it.