Solved

Simple excel report

Posted on 2012-03-19
5
313 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
beaten to it with longer post :P
0
 

Author Comment

by:SMadhavi
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

744 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

16 Experts available now in Live!

Get 1:1 Help Now