[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Removing entries from a spreadsheet with 30,000 rows

I have an Excel Spreadsheet from a pivot table in a SQL. I need to save this document as a CSV file so that it can be imported into another database but we need to remove entries and as it contains sensitive information. If I have a list of exclusions, what is the quickest and easiest way to remove that line? There are about 75 exclusions all on another spreadsheet.

Can you also guide me as to how to do it as my excel skills are not that great.

Thank you all in advanced.
0
MSSC_support
Asked:
MSSC_support
1 Solution
 
etech0Commented:
Maybe you want to try a find and replace?

If you post your file we can guide you better.
0
 
Steven HarrisPresidentCommented:
If you want to remove a row based on a condition, you need to look into VBA.  If you have some examples or a copy of the workbook as etech0 suggested, we can figure something out for you.
0
 
MSSC_supportAuthor Commented:
maybe a vlookup? I tried find and replace but I want the whole row gone for these entries.

For example we have a product description. If it matches the excusions then that row that it sits on needs to be hidden.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Steven HarrisPresidentCommented:
That would be VBA.
0
 
Rob HensonFinance AnalystCommented:
This could be achieved without VBA if so required but with the addition of a helper column.

The helper column would be a check to see of the product description on each row is found in the list of exclusions and if so flags as Delete.

You can then apply a Filter on the data and then have two options:

1) show only those not to be deleted; copying this data to separate sheet would not include the rows hidden by the filter. Saving the file as CSV when the copied sheet is active will then only save that sheet.
2) show those to be deleted, highlight the rows and delete; only those visible will be deleted leaving the remaining rows for then saving as CSV.

Alternatively you can use an Advanced Filter to extract required data to separate sheet.

One point to note when using a field such as description, will all descriptions be exactly the same eg different abbreviations for aspects of the description such as LH  or L/H for Left Hand.

Thanks
Rob H
0
 
MSSC_supportAuthor Commented:
I managed to resolve this using MS Access
0
 
MSSC_supportAuthor Commented:
Used Access to create linked tables and then remove the matching entries.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now