How to create a Dynamic List that Automatically Lists Items that Match Certain Criteria

[franswa3434]

Hi,

I am trying to create a dynamic list that automatically lists items that match certain criteria. With my example below, I want all the blue and green items (as labeled in Column B) in Column A to be put into a list in Column E. This list also needs to be dynamic as more items are added to filter through and add only the "blue and green" items to the list.

Thank you anyone and everyone who is willing to help!

A</SPAN>	B</SPAN>	C </SPAN>	D </SPAN>	E</SPAN>
Item</SPAN>	Color</SPAN>			Blue and Green Items</SPAN>
Grass</SPAN>	Green</SPAN>			Grass
Water</SPAN>	Blue</SPAN>			Water
Fire</SPAN>	Red</SPAN>			Sky
Dirt</SPAN>	Brown</SPAN>			Go Light
Sky</SPAN>	Blue</SPAN>			Car
Go Light	Green
Car</SPAN>	Blue</SPAN>

<TBODY>
</TBODY>

 

[XOR LX]

Hi.

Put this formula in C1:

=SUM(COUNTIF(B2:B8,{"Blue","Green"}))

Then this array formula** in your first cell of choice:

=IF(ROWS($1:1)>$C$1,"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8={"Blue","Green"},ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1),ROWS($1:1))))

Copy this formula down (though not the one in C1) until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Aladin Akyurek]

Item	Color	X	Y	Blue and Green Items
Grass	Green			Grass
Water	Blue			Water
Fire	Red			Sky
Dirt	Brown			Go Light
Sky	Blue			Car
Go Light	Green
Car	Blue

<TBODY>
</TBODY>

Select A1:E8.
Activate Insert | Table.

E2, control+shift+enter, not just enter, and copy down to E8...

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(MATCH($B$2:$B$8,{"Blue","Green"},0)),
  ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($E$2:E2))),"")

 

[franswa3434]

Thanks to both of you! They both work exactly how I want them to!

 

[Aladin Akyurek]

Thanks to both of you! They both work exactly how I want them to!

In the Table version, the target formula is automatically copied down for every addition of a record.