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
|