APPLYING MULTIPLE FILTERS IN EXCEL

Question

I have a table with names in A and numbers in S. I want to use the filter function by selecting a criterion in A and top 'n' values in S.

link for the file

https://1drv.ms/x/s!Ate5wE2joLO0goNCs-naRo4v-9f8yw?e=jQ6Xui

Answer 1

Hi @HM PEMSRJY

Could you please tell us more details? Such as give us a sample to show the result that you need after selecting a criterion in A and top 'n' values in S.

According to your Excel file, you can slect the table, go to Home tab > Editing group > Sort & Filter > Filter.

Then select the BRANCH names that you want, select Number Filter for TOT, and click Top 10..., set the 'n' number to show top 'n' values. But please note, the top 'n' values are based on the original data, not the data after you select out BRANCH names.

If you want to show top 'n' values based on data after you select out BRANCH names, I suggest you use Advanced Filter.

• Please go to Data tab > Sort & Filter group > Advanced > Choose 'Copy to another location'.
• The list range is the whole original table includes the titles, the Criteria range is the BRANCH names list that you want to filter out. Please note, the title for Criteria range and the tile for the filter column in List range must be same.
• Then Copy to is the starting position of the filter results.
• After select the BRANCH names, you can use Top 10... feature to show the top 'n' values.

Any issues, welcome to post back.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

I hope it's closer to your wishes

insert a table,

jpeg of sorting and order.

Answer 3

I hope I can be of some help to you.

https://1drv.ms/x/s!At950JyIeN6wgeoLZLG-Z_YhDjVShA?e=cPSACa

Download and save, open then enable macros

each select Q1:Q3

and click 【Sort】 button

if you didn't work the 【Sort】 button,

Execute macro [Sort] by viewing macros.

The selection of Q1:Q3 will change the result.

---

Below is just a recording of the macro.

Option Explicit
Sub Sort()
'
' Sort Macro
'

'
    Range("P5:AC15").Select
    Selection.Copy
    Range("P20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "AC21:AC30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("P20:AC30")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
            ' by Nobuko IT
End Sub