Filter values using a formula

[thekaoboy]

Suppose I have the following data in Columns A and B:


Green 5
Red
Blue 32
Orange 12
Yellow
White 622

In a separate tab, I would like to create a list that returns only those data items that meet certain criteria (e.g., only nonblanks). So my list would skip Red and Yellow in my example.

It seems like a pretty straightforward formula but I can't figure it out.
Can anyone please help?

Thanks.

 

[meldoc]

Hello

try.

Excel Workbook
	A	B
1	Green	5
2	Red
3	Blue	32
4	Orange	12
5	Yellow
6	White	622
Sheet1

Excel 2010

Excel Workbook
	A	B
1	Green	4
2	Blue
3	Orange
4	White
5
6
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B1	=COUNT(Sheet1!B1:B6)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

And copy the formula A1 down as far as you need.

 

[T. Valko]

Suppose I have the following data in Columns A and B:


Green 5
Red
Blue 32
Orange 12
Yellow
White 622

In a separate tab, I would like to create a list that returns only those data items that meet certain criteria (e.g., only nonblanks). So my list would skip Red and Yellow in my example.

It seems like a pretty straightforward formula but I can't figure it out.
Can anyone please help?

Thanks.

Try this...

With your data...

Book1
	A	B
1	Color	Values
2	Green	5
3	Red	_
4	Blue	32
5	Orange	12
6	Yellow	_
7	White	622
Sheet1

And the summary area on Sheet2...

Book1
	A	B
1	Count	Color
2	2	Red
3	_	Yellow
4	_	_
Sheet2

In the formulas I use the follwong defined named ranges:

• Color
• Refers to: =Sheet1!$A:$A
• Values
• REfers to: =Sheet1!$B$2:$B$7

Enter this formula in A2. This will return the count of records that meet the criteria.

=COUNTBLANK(Values)

Enter this array formula** in B2 and copy down until you get blanks.

=IF(ROWS(B$2:B2)>A$2,"",INDEX(Color,SMALL(IF(Values="",ROW(Values)),ROWS(B$2:B2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[markmzz]

Thekaoboy,

Try the following (for only one worksheet).

Assuming your data is in the range A5:B10 in the worksheet Plan1, type the following in the cells indicated:

A1 - Column1
B1 - Column2
B2 - <>
A4 - Column1
B4 - Column2

Then do the following (for Excel 2003):

On the Data menu, point to Filter, and select Advanced Filter.
Then, type the following (in the indicated fields on the dialog box Advanced Filter):

List range: A4:B10
Criteria range: A1:B2
Copy to: F4

And mark the Select Box Copy to another local.

Finally, click OK.

For Excel 2007, do the following:

Click in Data tab, and, in the group Filter and Sort, click in Advanced.
Then, type the following (in the indicated fields on the dialog box Advanced Filter):

List range: A4:B10
Criteria range: A1:B2
Copy to: F4

And mark the Select Box Copy to another local.

Finally, click OK.

Markmzz