Solved

Removing entries from a spreadsheet with 30,000 rows

Posted on 2013-11-05
7
345 Views
Last Modified: 2013-11-23
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
Comment
Question by:MSSC_support
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39624627
Maybe you want to try a find and replace?

If you post your file we can guide you better.
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39625148
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
 

Author Comment

by:MSSC_support
ID: 39626777
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 18

Expert Comment

by:Steven Harris
ID: 39627126
That would be VBA.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39627166
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
 

Accepted Solution

by:
MSSC_support earned 0 total points
ID: 39656525
I managed to resolve this using MS Access
0
 

Author Closing Comment

by:MSSC_support
ID: 39671073
Used Access to create linked tables and then remove the matching entries.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question