deleting rows in large database (+20k rows)
 

i need to delete all rows whose account numbers do not match a set of 20-30
account numbers. since the database is quite large was planning to use Union
application to create range of rows to be deleted.

what is easiest method to distinguish between rows containing the 20-30
account numbers i want to save and the hundreds which need to be deleted?

deleting rows in large database (+20k rows)
 

No macros are necessary:

in sheet1 A1:A30 put in your 20 or 30 values

in the next empty column in the sheet with your data (assume account numbers
are in column C, assume the empty column is M) so in M2

=Countif(Sheet1!$A$1:$A$30,C2)

fill down the column and apply an autofilter. Filter on zero

select all the rows except row 1. Delete the rows. Only the visible rows
will be deleted. Remove the Autofilter.

dleete the "dummy" column.

--
Regards,
Tom Ogilvy


"mwam423" wrote:

i need to delete all rows whose account numbers do not match a set of 20-30
account numbers. since the database is quite large was planning to use Union
application to create range of rows to be deleted.

what is easiest method to distinguish between rows containing the 20-30
account numbers i want to save and the hundreds which need to be deleted?

deleting rows in large database (+20k rows)
 

I would insert a new worksheet.
Put the 20 to 30 account numbers in A1:A20 (or A30)

Then I would insert a new column adjacent to the column with all the account
numbers in it. I had the account numbers in A1:a20000 and inserted a new column
B.

deleting rows in large database (+20k rows)
 

hi tom, appreciate the reply, as well as the nifty use of countif formula =D
database is also fodder for pivot table, if using a dynamic data source are
there any issues i need to know about?

"Tom Ogilvy" wrote:

No macros are necessary:

in sheet1 A1:A30 put in your 20 or 30 values

in the next empty column in the sheet with your data (assume account numbers
are in column C, assume the empty column is M) so in M2

=Countif(Sheet1!$A$1:$A$30,C2)

fill down the column and apply an autofilter. Filter on zero

select all the rows except row 1. Delete the rows. Only the visible rows
will be deleted. Remove the Autofilter.

dleete the "dummy" column.

deleting rows in large database (+20k rows)
 

hi dave, thanks for the response. your solution, and tom's above, are
basically the same; appreciate the help (our whole office appreciates your
help), you guys are real pros =D

"Dave Peterson" wrote:

I would insert a new worksheet.
Put the 20 to 30 account numbers in A1:A20 (or A30)

Then I would insert a new column adjacent to the column with all the account
numbers in it. I had the account numbers in A1:a20000 and inserted a new column
B.

Then with those 20-30 account numbers in Sheet2, I used this formula in B1:

=isnumber(match(a1,sheet2!a:a,0))
and dragged down all the rows.

Then I'd convert that column B to values
(select column B, edit|Copy, edit|Paste special|Values)

Next I'd sort that data by column B.

And then apply data|Filter|autofilter to that column B.

Show the false values
Delete the visible rows

and then remove the filter.