If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Deleting rows based on column values
Have a spreadsheet with 30,000 rows. I need to delete rows that have
specific column values, for example: Column A = 61 Column B = 60-67 Column C = PAY Column A = 61 Column B = 70-73 or 100 Column C = CTF Column A = 61 Column B = 72 Column C = WDI Column A = 61 Column B = 84 or 120-126 Column C = JDO or JDG .... And many more. If someone could point me in the right direction as to how to set this up, it would be greatly appreciated. Thank you so very much. |
#2
|
|||
|
|||
Deleting rows based on column values
One way is via Autofilter
Try on a spare copy (always try things on a spare copy) 1. Apply Autofilter*, then filter it by your specific criteria** for cols A to C 2. Select all the filtered rows (select the blue row headers) Right-click Delete Row. 3. Re-set it to show all remaining rows via Data Filter Show All Repeat to filter again by the next criteria set delete ... as required *Ensure its correctly applied all the way through the data by selecting the entire cols before clicking Data Filter Autofilter. Don't rely on Excel's auto-sense. Intervening blank cells in data will disrupt the range covered **Use the "Custom" bit in the autofilter dropdown. Since you have 30k rows range, not all uniques may show in the dropdown (there's a limit of 1000 items in xl03 or earlier). Anyway, you would need the Custom Autofilter dialog to specify a range of values to filter (eg: col B = 60-67) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote: Have a spreadsheet with 30,000 rows. I need to delete rows that have specific column values, for example: Column A = 61 Column B = 60-67 Column C = PAY Column A = 61 Column B = 70-73 or 100 Column C = CTF Column A = 61 Column B = 72 Column C = WDI Column A = 61 Column B = 84 or 120-126 Column C = JDO or JDG ... And many more. If someone could point me in the right direction as to how to set this up, it would be greatly appreciated. Thank you so very much. |
#3
|
|||
|
|||
Deleting rows based on column values
Max, that was very helpful. It cut my clean up time by more than 60%.
Thanks again! "Max" wrote: One way is via Autofilter Try on a spare copy (always try things on a spare copy) 1. Apply Autofilter*, then filter it by your specific criteria** for cols A to C 2. Select all the filtered rows (select the blue row headers) Right-click Delete Row. 3. Re-set it to show all remaining rows via Data Filter Show All Repeat to filter again by the next criteria set delete ... as required *Ensure its correctly applied all the way through the data by selecting the entire cols before clicking Data Filter Autofilter. Don't rely on Excel's auto-sense. Intervening blank cells in data will disrupt the range covered **Use the "Custom" bit in the autofilter dropdown. Since you have 30k rows range, not all uniques may show in the dropdown (there's a limit of 1000 items in xl03 or earlier). Anyway, you would need the Custom Autofilter dialog to specify a range of values to filter (eg: col B = 60-67) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote: Have a spreadsheet with 30,000 rows. I need to delete rows that have specific column values, for example: Column A = 61 Column B = 60-67 Column C = PAY Column A = 61 Column B = 70-73 or 100 Column C = CTF Column A = 61 Column B = 72 Column C = WDI Column A = 61 Column B = 84 or 120-126 Column C = JDO or JDG ... And many more. If someone could point me in the right direction as to how to set this up, it would be greatly appreciated. Thank you so very much. |
#4
|
|||
|
|||
Deleting rows based on column values
Welcome, glad it helped.
Thanks for rating and feedback. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote in message ... Max, that was very helpful. It cut my clean up time by more than 60%. Thanks again! |
Thread Tools | |
Display Modes | |
|
|