Formulas: Advanced Filters

Cronina · Oct 10th 2003

I have attached a spreadsheet with information that I would like to break down by the top 10 people in three different categories.

I need to find out the top 10 for people under colums:
ACTSMTD, PWMTD AND IWMTD

I hope you can help me design a filter to place these top 10 people to the right of this worksheet

Will Riley · Oct 10th 2003

Cronina - I think we currently have a problem with attaching files. i.e. it doesn't work.

I'm hoping it gets fixed ASAP.

I guess it will be difficult to solve your issue wiithout seeing the file so try to bear withus.

Will

Cronina · Oct 10th 2003

Name FirstCol SecondCol ThirdCol 4thCol
Name One 7 $13 136 $189
Name Two 3 $10 112 $398
Name Three 1 $18 158 $115
.......
Name TwoThousand etc...

This is sort of how the data is set up and I need to find out who the top 10 people for each of the four colums are. If this helps great.

rem1224 · Oct 11th 2003

Hi Cronina,

Try the following macro - it just uses the built in AutoFilter function with the "Top 10" selection then copy and paste the results to the right. (Assumes your data is in Col A - E)

Code

Sub Macro1()
  'Turn AutoFilter On
    Range("A1").Select
    Selection.AutoFilter
  
  'Filter Top10 from Col 2 and copy to Col G
    Selection.AutoFilter Field:=2, Criteria1:="10", Operator:=xlTop10Items
    Columns("A:E").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    
   'Reset Col 2 and get Top10 from Col 3 and Copy
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=3, Criteria1:="10", Operator:=xlTop10Items
    Columns("A:E").Select
    Selection.Copy
    Range("M1").Select
    ActiveSheet.Paste
    
   'Reset Col 3 and get Top10 for col 4 and Copy
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items
    Columns("A:E").Select
    Selection.Copy
    Range("S1").Select
    ActiveSheet.Paste
    
   'Reset Col 4 and get Top10 for col 5 and Copy
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5, Criteria1:="10", Operator:=xlTop10Items
    Columns("A:E").Select
    Selection.Copy
    Range("Y1").Select
    ActiveSheet.Paste
    
   'Turn off the Autofilter
    Range("A1").Select
    Selection.AutoFilter
End Sub

Display More

.....Ralph

Formulas: Advanced Filters

Participate now!

Share