By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,653 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,653 IT Pros & Developers. It's quick & easy.

Comparing data in two columns in excel VBA

P: 82
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
Dec 16 '11 #1

✓ answered by 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 :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub FixM()
  4.     Dim lngFrom As Long, lngTo As Long
  5.     Dim strVal As String
  6.     Dim ranA As Range
  7.  
  8.     lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2))
  9.     lngTo = Val(Split(ActiveSheet.UsedRange.Address, "$")(4))
  10.     For Each ranA In Range("A" & lngFrom & ":A" & lngTo)
  11.         strVal = ranA
  12.         If strVal > "" Then
  13.             With Range("M" & lngFrom & ":M" & lngTo)
  14.                 If .Find(What:=strVal, _
  15.                          LookIn:=xlValues, _
  16.                          LookAt:=xlWhole) Is Nothing Then _
  17.                     Range("M" & lngTo + 1).End(xlUp).Range("A2") = strVal
  18.             End With
  19.         End If
  20.     Next ranA
  21. 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.

Share this Question
Share on Google+
18 Replies


NeoPa
Expert Mod 15k+
P: 31,712
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 :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub FixB()
  4.     Dim lngFrom As Long, lngTo As Long
  5.     Dim strVal As String
  6.     Dim ranA As Range, ranB As Range
  7.  
  8.     lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2))
  9.     lngTo = Val(Split(ActiveSheet.UsedRange.Address, "$")(4))
  10.     For Each ranA In Range("A" & lngFrom & ":A" & lngTo)
  11.         strVal = ranA
  12.         If strVal > "" Then
  13.             With Range("B" & lngFrom & ":B" & lngTo)
  14.                 If .Find(What:=strVal, _
  15.                          LookIn:=xlValues, _
  16.                          LookAt:=xlWhole) Is Nothing Then _
  17.                     Range("B" & lngTo + 1).End(xlUp).Range("A2") = strVal
  18.             End With
  19.         End If
  20.     Next ranA
  21. End Sub
Dec 16 '11 #2

P: 82
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.
Dec 16 '11 #3

NeoPa
Expert Mod 15k+
P: 31,712
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?
Dec 16 '11 #4

P: 82
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.
Dec 16 '11 #5

kadghar
Expert 100+
P: 1,295
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:

Expand|Select|Wrap|Line Numbers
  1. Sub Columns()
  2. Dim a
  3. Dim b
  4. a = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
  5. b = Range(Cells(1, 2), Cells(1, 2).End(xlDown))
  6. Dim c() As String
  7. Dim n As Integer
  8. For i = 1 To UBound(a)
  9.     Dim boo1 As Boolean
  10.     boo1 = False
  11.     For j = 1 To UBound(b)
  12.         If a(i, 1) = b(j, 1) Then
  13.             boo1 = True
  14.             Exit For
  15.         End If
  16.     Next
  17.     If Not boo1 Then
  18.         n = n + 1
  19.         ReDim Preserve c(1 To n)
  20.         c(n) = a(i, 1)
  21.     End If
  22. Next
  23. Dim d() As String
  24. ReDim d(1 To n, 1 To 1)
  25. For i = 1 To n
  26.     d(i, 1) = c(i)
  27. Next
  28. Range(Cells(UBound(b) + 1, 2), Cells(UBound(b) + n, 2)) = d
  29. End Sub
HTH
Dec 16 '11 #6

P: 82
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.
Dec 16 '11 #7

kadghar
Expert 100+
P: 1,295
then in line 12 just change cases for comparing:

Expand|Select|Wrap|Line Numbers
  1.         If ucase(a(i, 1)) = ucase(b(j, 1)) Then
  2.             boo1 = True
  3.             Exit For
  4.         End If
Dec 16 '11 #8

NeoPa
Expert Mod 15k+
P: 31,712
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 :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub FixM()
  4.     Dim lngFrom As Long, lngTo As Long
  5.     Dim strVal As String
  6.     Dim ranA As Range
  7.  
  8.     lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2))
  9.     lngTo = Val(Split(ActiveSheet.UsedRange.Address, "$")(4))
  10.     For Each ranA In Range("A" & lngFrom & ":A" & lngTo)
  11.         strVal = ranA
  12.         If strVal > "" Then
  13.             With Range("M" & lngFrom & ":M" & lngTo)
  14.                 If .Find(What:=strVal, _
  15.                          LookIn:=xlValues, _
  16.                          LookAt:=xlWhole) Is Nothing Then _
  17.                     Range("M" & lngTo + 1).End(xlUp).Range("A2") = strVal
  18.             End With
  19.         End If
  20.     Next ranA
  21. 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.
Dec 16 '11 #9

P: 82
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))
Dec 16 '11 #10

kadghar
Expert 100+
P: 1,295
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)
Dec 16 '11 #11

100+
P: 759
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).

Expand|Select|Wrap|Line Numbers
  1. Sub AddToB()
  2. Dim cA As Long
  3.     cA = 1 ' column A
  4. Dim cB As Long
  5.     cB = 2 'column B
  6. Dim rA As Long, rB As Long
  7.  
  8.     rA = 1 'First row in A column (if you have a column header,: rA=2)
  9.     Do Until IsEmpty(Cells(rA, cA))
  10.         rB = 1 'First row in B column (if you have a column header,: rB=2)
  11.         Do Until IsEmpty(Cells(rB, cB))
  12.             If Cells(rB, cB) = Cells(rA, cA) Then
  13.                 GoTo Next_rA
  14.             End If
  15.             rB = rB + 1
  16.         Loop
  17.         ' No cell in rB = Ra
  18.         Cells(rB, cB) = Cells(rA, cA)
  19. Next_rA:
  20.         rA = rA + 1
  21.     Loop
  22. End Sub
Dec 17 '11 #12

NeoPa
Expert Mod 15k+
P: 31,712
@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.
Dec 17 '11 #13

P: 82
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.
Dec 18 '11 #14

NeoPa
Expert Mod 15k+
P: 31,712
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.
Dec 18 '11 #15

kadghar
Expert 100+
P: 1,295
@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.
Dec 18 '11 #16

NeoPa
Expert Mod 15k+
P: 31,712
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.
Dec 19 '11 #17

P: 82
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?
Dec 19 '11 #18

NeoPa
Expert Mod 15k+
P: 31,712
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 20 '11 #19

Post your reply

Sign in to post your reply or Sign up for a free account.