Solved

Simple excel report

Posted on 2012-03-19
5
314 Views
Last Modified: 2012-08-14
I have never worked with excel before simplequestion but just need to get this done ASAP.
There is a sheet which has 40000 plus records
col1   col2   col3   col4 col5


I need to pull out data (rows)from this sheet into a new one such that value of col3 is in (val1,val2,val3,val4,val5,val6,val6,val8,val9,val10)  I should be getting approx 20000 records. How do I do this in excel.

YRKS
0
Comment
Question by:SMadhavi
  • 3
5 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37738218
put autofilter on the sheet - in data, filtering, then filter column three, go to custom and create the custom filter..

then once filtered, copy and past the records into a new sheet.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 400 total points
ID: 37738229
Am I correct in re-stating your problem like so?

You have a worksheet with five columns of data.  You want to see (i.e., copy/move) only the rows where the third column contains a specific set of values.  Your example lists ten possible values.

If you are using Excel 2007/2010, you can use an auto-filter to view only those values.  Or, you can use the same auto-filter to view all values except those and then delete those rows, leaving only the ones you want to view.

To turn on filters: click "Data" in the menu on top, then click the "Filter" icon. Small drop arrows will appear on the top row.  Click the drop arrow to see values in that column and filter from there.  You can select individual items via check boxes or use more general filtering (ex., number or text matching).

See the attached workbook as an example.  The data is filtered to show only  rows where Column3 values are 1,2,3,4, or 5.

-Glenn
EE-DataFiltering.xlsx
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 100 total points
ID: 37738231
depending on the values you might be able to get clever with the customer filter using "contains".

for a large record set, and because i am used to access and sql, i would import the data set into access or sql, and write a query to return the records....

you can highlight the data set and go to data, advanced filter, it lets you specify a criteria range... e.g. cella a1..a10 to contain your search values....
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37738236
beaten to it with longer post :P
0
 

Author Comment

by:SMadhavi
ID: 37738535
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Hlookup formula help 14 20
Excel - list cell contents that are not duplicated 4 30
time format showing wrong 12 51
Update As Well As Add 6 38
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now