Listing only values above 6

[kevincoxshall]

Dear all

I am lookong for the code to give the values greater than 6 from the list on the left.
I can do this quickly by sorting but my data keeps changing and it would be even quicker with code.

Hope you can help


Kevin

[Pete_UK]

Why don't you just apply auto-filter to column B, and use the Number Filter option on the drop-down to choose Greater than or Equal to and then put 6 in the dialogue box.

Hope this helps.

Pete

[bebo021999]

Try in E2:

=INDEX(A$1:A$45,SMALL(IF($B$3:$B$45>6,ROW($B$3:$B$45)-ROWS($B$3)+1,""),ROW(A1)))

This is an ARRAY formula, should be confirmed by CTRL-SHIFT-ENTER.
Drag down and across untill getting error signs

[XOR LX]

Non-array alternative for E2:

=IFERROR(INDEX($A$3:$A$45,SMALL(INDEX(($B$3:$B$45>6)*(ROW($B$3:$B$45)-MIN(ROW($B$3:$B$45))+1),,),ROWS($B$3:$B$45)-COUNTIF($B$3:$B$45,">6")+ROWS($1:1))),"")

Regards

[RobertMika]

For your Excel 2003 version:

Copy of greater than 6.xls

[XOR LX]

Edit: didn't see you were on 2003.

=IF(ROWS($1:1)>COUNTIF($B$3:$B$45,">6"),"",INDEX($A$3:$A$45,SMALL(INDEX(($B$3:$B$45>6)*(ROW($B$3:$B$45)-MIN(ROW($B$3:$B$45))+1),,),ROWS($B$3:$B$45)-COUNTIF($B$3:$B$45,">6")+ROWS($1:1))))

Regards

[newdoverman]

As I understand it, you want both the name and the grade.

In F2 enter this formula with Ctrl + Shift + Enter and copy down.

=IFERROR(SMALL(IF(B:B>6,B:B),ROW()-1),"")

In E2 enter this formula and copy down:

=IFERROR(INDEX($A$3:$A$45,MATCH(F2,$B$3:$B$45,0)),"")

[kevincoxshall]

greater than 2.1.xls

Thanks everyone for the replies.

I got it working but the values in each cell are found from a pivot table making each cell appear blank but actually they are bound by a formula. So the previous formula is preventing it from working.

I have now attached the pivot table all all routes from the original data.

Hopefully it will be a small tweek to get it working?

Kevin