Filtering a list of data to a separate sheet

ATSJ

Board Regular
Joined
Dec 7, 2010
Messages
58
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You might need to use ADVANCED FILTER and copy the filtered cell to your specified location
 
Upvote 0
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?
 
Upvote 0
Hi

Consider the following:
ABCDE
1CodeValueCriteria
2W1W1
3W22
4W33
5L46
6L5
7W6

<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
 
Last edited:
Upvote 0
Thank you, works perfectly and I appreciate it.

Hi

Consider the following:
ABCDE
1CodeValueCriteria
2W1W1
3W22
4W33
5L46
6L5
7W6

<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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top