Populate a list base on Rating

[genuineblonde]

I have 2 column list. Column A has employee ranking. Column B has employee names. I need to pull the names of the employees with "Excellent" Ranking to another worksheet. Can anyone help me with the formula to use? I don't want to use a VB.

 

[bluedragon2]

Sort them based on rankings, then copy and paste.

Do you need something more automated?

 

[Zig1]

Another method would be to turn on the Auto Filter and select the "Excellent" Rank.

Then copy past the results to the other worksheet.

 

[genuineblonde]

It needs to fully automated. Thanks for the suggestion on the Autofilter, but it needs to be a separate list in another area of the worksheet.

 

[plantj]

Try advanced filter.

Say you name the full list range "rngAll".

On a separate sheet create a 2 row range with the first row containing the heading for the ranking (as in the main range) and under it put the "excellent" rank. Name this range, say, "rngCriteria"

So rngAll is like this perhaps...

name rank
bob good
james poor
jenny excellent
keith good
pol excellent
slim poor

and rngCriteria is like this...

name rank
excellent

Next name the top left corner of the output range "rngOut".

Then use Data | Advanced filter. Set Action to "Copy to another location". Type rngAll in "List range" and rngCriteria in (you guessed it) "Criteria range" and "Copy to" rngOut. The star employees should appear in the output range...

name rank
jenny excellent
pol excellent