Finding values in a range of cells

[ulicki]

Hi,

Using Excel 9.0.

I have 2 columns of cells about 35,000 rows each. The data in the cells is text. I need to compare the columns to each other. I need to determine values in column A that are not in column B and vice versa. I was using vlookup and match to do this and then auto filter my results. It works but it is so slow. It is taking like 10 minutes for the calculation. I have a pretty good PC (pentium 3, 500MB ram), so I do not think that is the problem. There will only be may up to 100 entries that do not have a match.

So is there a quicker way to achieve my goal?

Thanks,

Mike

 

[SkipVought]

Hi,

I ran as quik 'n' dirty on about 40k rows using the MATCH function and it returned results in an instant.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[ulicki]

Hi Skip,

Thanks,

I tried running for numbers and it goes about 10 times faster, although it still takes about a minute or two. Maybe it is my version of Excel or my PC. My processor is maxed when this function is run.

Mike

 

[ulicki]

Skip,

Did you match with zero, i.e. match(m5,$c$5:$c$35250,0)?

When I use match(m5,$c$5:$c$35250,1) it goes quick, but not with zero.

Mike

 

[SkipVought]

Using ZERO is EXACT MATCH.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[ulicki]

Hi Skip,

Yes I know.

However please note that I am comparing 2 columns of 35,000 rows. I line the columns up and in the first row of a third column, I put the formulas in (see above). I then auto fill the rest of my rows and this is what is taking a long time.

Thanks for responding,

Mike