473,836 Members | 1,265 Online

# Comparing data in two columns in excel VBA

82 New Member
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
18 6858
1,295 Recognized Expert Top Contributor
b is 2
m is 13 (hope so)

so just
b= range(cells(1,1 3), 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
Mihail
759 Contributor
You can customize how Excel show column's names.
Use the Office button (Top left side - colored as a rainbow).

The single requirement is to have NOT empty cells in rows you are compare (A, B).

Expand|Select|Wrap|Line Numbers
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
32,584 Recognized Expert Moderator MVP
@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.

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
starlight849
82 New Member
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
32,584 Recognized Expert Moderator MVP
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
1,295 Recognized Expert Top Contributor
@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
32,584 Recognized Expert Moderator MVP
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
starlight849
82 New Member
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.

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
32,584 Recognized Expert Moderator MVP
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