Excel/Which Excel Function to use?
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.
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.
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.