[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Simple excel report

Posted on 2012-03-19
5
Medium Priority
?
323 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 1600 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 400 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 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