Find Results in excel copy the rows to another sheet

Question

I need some help copying data from one excel worksheet to another. For example:

Sample Data

__A__B___C

1 aaa bbb ddd

2 bbb ccc eee

3 ccc fff rrr

4 ccc fff ttt

5 ddd eee ggg

6 aaa ddd eee

7 bbb fff hhh

8 eee eee eee

So for the above records if I do a 'CTRL + F' and search to find All 'eee' the results will show 6 instances in C2, B5, C6, A8, B8, C8

Now I want to copy the whole of rows 2,5,6 and 8 to another worksheet.

Peter L. · Accepted Answer · 2013-02-06 14:33:58Z

up vote 1 down vote accepted

To achieve what you need please do the following (assuming your data is located in columns A:C starting row 2, i.e. there are headers):

Type in D2 (additional column): =IF(OR(A2:C2="eee"),1,0), but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
Autofill formula as required.
Add filter to the whole A:D range.
Apply filter to column D for value 1.
Copy entire filtered columns A:C and paste anywhere - only filtered rows will be copied.

answered Feb 6 at 14:33

Peter L.
3,5942828

	That's Brilliant! – amateur Feb 6 at 14:46
	One other thing though, can I edit that formula to search for multiple keywords such as for 'eee' or 'fff' and the result for 'eee' to be 1 and for 'fff' to be '2' Many Thanks! – amateur Feb 6 at 14:47
	@amateur what if you have BOTH `eee` and `fff` in the same row - what you should get? 1? 2? or 1+2? be more specific. – Peter L. Feb 6 at 15:04
	aah didn't think about that, never mind....what you have answered already does the trick. Thank you very much for your help :) – amateur Feb 6 at 15:07
	@amateur please download the updated sample using the same link - I added 2 more solutions: 2nd will count SUM of match indexes, 3rd will count number of total matches in a single row. Hope that was helpful) – Peter L. Feb 6 at 15:22

Ekonion · Answer 2 · 2013-02-06 14:18:35Z

up vote 0 down vote

I think what you are looking for is called VLOOKUP just take the Excel-help, theres everything explained

answered Feb 6 at 14:18

Ekonion
169118

	VLOOKUP is a function that searches for value in the left-most column of table_array and returns the value in the same row based on the index_number. I need something like a macro perhaps where I can specify a criteria (keyword) to search for, it returns the data and copies all rows containing that criteria to a new sheet. – amateur Feb 6 at 14:25
	ah.. sry.. misunderstood ^^ didnt work with excel for a long time, so i think i cant you help then... Will have a look, when my workday is done. But will take a while... – Ekonion Feb 6 at 14:32

2 Answers