Countif speed, Excel 2007 200,000 rows

[Jeffrey Green]

I have to worksheets, each with about 200,000 rows.

I only need to know if a VIN on sheet 1 lives on sheet 2. maybe 195,000 of the rows in sheet 1 do live in sheet two, so I can delete them.

I named the VIN column on Sheet 2 "VIN", and added a column B on sheet 1 and used =Countif(VIN,A2). If it is a 1, I can delete the row, if it is a 0, i need to leave it.


It takes about 45 minutes to do this . . . is there a faster way to simply identify the VINs on sheet 1 that live on Sheet 2??

Thanks

 

[Aladin Akyurek]

I have to worksheets, each with about 200,000 rows.

I only need to know if a VIN on sheet 1 lives on sheet 2. maybe 195,000 of the rows in sheet 1 do live in sheet two, so I can delete them.

I named the VIN column on Sheet 2 "VIN", and added a column B on sheet 1 and used =Countif(VIN,A2). If it is a 1, I can delete the row, if it is a 0, i need to leave it.


It takes about 45 minutes to do this . . . is there a faster way to simply identify the VINs on sheet 1 that live on Sheet 2??

Thanks

Try...

=MATCH(A2,VIN,0)

Then activate AutoFilter with the Custom option of Greater than 0. And delete the selected records.

 

[Jeffrey Green]

thanks,

So, MATCH is faster than Countif or a Vlookup?

 

[Matty]

Have you tried using the MATCH function with match_type set to 0? You would then keep the #N/As (if I follow correctly).

Matty

 

[Aladin Akyurek]

thanks,

So, MATCH is faster than Countif or a Vlookup?

Yes, MATCH is definitely faster than COUNTIF, even with match-type set to 0.

MATCH(A2,VIN,0)

VLOOKUP(A2,VIN,1,0)

would be comparable.

 

[mirabeau]

Jeffrey,

Just curious but:

1. How long does it take to do your 200,000 rows with most recent procedure.

2. What is VIN? Vehicle Identification Number, I would guess.

 

[AltNrg4U]

If you want to go really fast, make Excel cheat for you if you can have the VIN be sorted.
=if(VLOOKUP(A2,VIN,1,TRUE)=A2,VLOOKUP(A2,VIN,1,TRUE),"")

This works SO MUCH FASTER when you set vlookup (or match) to be an approximate match, then verify it is your exact match, then display your "approximate" match that you verified. I don't know why, but it took me from about 90 minutes in a large work book (87,000 records, but also around 42 columns to lookup) to about 2 minutes. Not bad eh?

 

[Aladin Akyurek]

If you want to go really fast, make Excel cheat for you if you can have the VIN be sorted.
=if(VLOOKUP(A2,VIN,1,TRUE)=A2,VLOOKUP(A2,VIN,1,TRUE),"")

This works SO MUCH FASTER when you set vlookup (or match) to be an approximate match, then verify it is your exact match, then display your "approximate" match that you verified. I don't know why, but it took me from about 90 minutes in a large work book (87,000 records, but also around 42 columns to lookup) to about 2 minutes. Not bad eh?

The reason is the algorithm LOOKUP and kindired functions with match-type set to TRUE (or 1) recruit. It must be a variant of binary search.