Fastest way for comparing columns?

T

Tony

Hi Group,

I am using Chip Pearon's excellent formula for comparing
values in various cols, i.e Col B & Col A and vice versa.
It works well but it has become very, very, very slow due
to the size of the ranges I'm working with (10,000+)
rows. Is there a faster method available (VBA?) to allow
me to produce similiar results
? Here is Chip's formula.

=IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))="","",IF
(COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)))
=0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),""))

Ideally I would like to produce the results...
if in Col A but not in Col B put in Col C
if in Col B but not in Col A put in Col D
If duplicates put in Col E

I amusing Excel 2000 on pentium 4.

Regards

Tony
 
A

Alan Hutchins

Hi,

I assume that the two columns are numbers - well my
example does anyway:

Other things to note - you can test for the last row, and
place that into the code instead of it being hard coded in
this example.

I used randbetween for column A and B to generate numbers
between 1 and 100 for 10,000 rows to see how long it took -
answers were populated within 8 seconds.

I am sure the code could be improved.......

Option Explicit

Sub try_this()
Dim intrwindex As Integer
Dim intcolindex As Integer
Dim int_cola As Integer
Dim int_colb As Integer

intcolindex = 1

For intrwindex = 1 To 10000
int_cola = Cells(intrwindex, intcolindex).Value
int_colb = Cells(intrwindex, intcolindex).Offset(0,
1).Value


If str_cola <> str_colb Then
Cells(intrwindex, intcolindex).Offset(0, 2).Value =
int_cola
Cells(intrwindex, intcolindex).Offset(0, 3).Value =
int_colb

Else
Cells(intrwindex, intcolindex).Offset(0, 4).Value =
int_cola
End If

Next intrwindex

End Sub

HTH
 
M

Mike Fogleman

Typically an Array Formula is the fastest way to compare 2 lists. Coupled
with an IF statement in column C, compare A to B, if result =0 then = the
value in A, if not then "". In column D do the same to compare B to A. Now
an IF statement in column E to to see if the cell in column C or D is "". If
C is "" then the value from A, If D is "" then value from B, If neither is
"", then "".

Chip's website can help with the array formula.

Mike F
 
T

Tony

Wow, that IS fast Alan. Many thanks indeed
Mike - will look at array formulas as suggested.

T.
 
Top