Matching cells are always side by side?

--
Regards,
Tom Ogilvy


"Tony" wrote in message
...
Wow, that IS fast Alan. Many thanks indeed
Mike - will look at array formulas as suggested.

T.
-----Original Message-----
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



-----Original Message-----
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



.

.