Solved

Simple excel report

Posted on 2012-03-19
5
319 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
[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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

690 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