View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Mike Fogleman Mike Fogleman is offline
external usenet poster
 
Posts: 1,092
Default Fastest way for comparing columns?

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

"Tony" wrote in 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(Rang e2),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