Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very large database (600k rows, 40 columns in xlsx) | Excel Discussion (Misc queries) | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
large database with multiple rows | Excel Worksheet Functions | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming |