Filtering a list of data to a separate sheet

[ATSJ]

I have a list on sheet 1 of two columns of data, A and B. Column A is a list with either a W or L. I would like to search through column A for all fields with a W and copy the entire row for each entry to sheet 2. Is it possible to do this using a variation of VLOOKUP and if so, how?

 

[Momentman]

You might need to use ADVANCED FILTER and copy the filtered cell to your specified location

 

[ATSJ]

You might need to use ADVANCED FILTER and copy the filtered cell to your specified location

I've tried it but it doesn't automatically update when changes are made to the data in the original list from what I can tell. I want something that updates, maybe that means I need something in VBA triggered by a Worksheet_change?

 

[myall_blues]

Hi

Consider the following:

	A	B	C	D	E
1	Code	Value		Criteria
2	W	1		W	1
3	W	2			2
4	W	3			3
5	L	4			6
6	L	5
7	W	6

<tbody>
</tbody>

The ARRAY formula (enter using CTRL+SHIFT+ENTER) in E2 is:

=IFERROR(INDEX(B$2:B$7,SMALL(IF(A$2:A$7=D$2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(B$2:B2))),"")

and copied down. When the formula runs out of matching values it will generate a blank.

Obviously you can put what is in D1:E5 onto a separate, new sheet if you want.

Regards

Murray

 

[ATSJ]

Thank you, works perfectly and I appreciate it.

Hi

Consider the following:

	A	B	C	D	E
1	Code	Value		Criteria
2	W	1		W	1
3	W	2			2
4	W	3			3
5	L	4			6
6	L	5
7	W	6

<tbody>
</tbody>

The ARRAY formula (enter using CTRL+SHIFT+ENTER) in E2 is:

=IFERROR(INDEX(B$2:B$7,SMALL(IF(A$2:A$7=D$2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(B$2:B2))),"")

and copied down. When the formula runs out of matching values it will generate a blank.

Obviously you can put what is in D1:E5 onto a separate, new sheet if you want.

Regards

Murray