You are here:

Excel/Which Excel Function to use?

Advertisement


Question
Hello,
I have an Excel 2010 worksheet, 7 columns of data (alpha and numeric) and approx 10,000 lines.  I want to search for a word in column 5 of the worksheet and where there is a match, I want to copy that line of data into a different worksheet in the same workbook.
Ex:

JC123  1234    1/1/16   S    ABC12     XYZ12    $15
JO123  1235    1/5/16   I    ABD13     XZY13    $20
SR123  1236    1/8/16   S    CUST      ZXY14    $25
BC123  1237    1/20/16  S    ABE14     UVW15    $30
JC145  1238    1/25/16  I    CUST      WVU16    $35
JO157  1239    1/31/16  S    ABF15     VUW17    $40

I want to find every instance in Column 5 that matches "CUST"
and copy that Line into another worksheet.  

I hope this is clear.
Thank you for your assistance.

Answer
You would probably use the Data Filter for this.  If you've never seen this before, you may realize you don't really have to copy it to another sheet.  I use the data filter on large database sets to deal with subsets of the data right there in place, without having to copy it elsewhere.

Anyway, assuming that row1 is your headers for each column and the data starts in in row2.

1) Highlight row1

2) Select the DATA tab and click on FILTER

Your row1 will now have a drop down in every cell across the top

3) Use the column E drop down and uncheck them all, then check only the CUST option

Now all the CUST rows are still showing, the rest are all hidden.


At this point you can deal with that data right there on the original sheet without the need for a separate sheet.   You can edit the  data, print that sheet, then filter another way to deal with a different subset, up to you.

You can also copy it to another sheet.   Just:

1) click on row1 of the still filtered results

2) Press CTRL-A to select all the visible rows

3) Press CTRL-C  to copy the visible rows and paste on another sheet.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.