The named ranges were created using

The named ranges were created using the INDEX function as shown below

Named Range Formula
prd =Master!$A$2:INDEX(Master!$A:$A,COUNTA(Master!$A:$A))
rgn =Master!$B$2:INDEX(Master!$B:$B,COUNTA(Master!$B:$B))
cust =Master!$C$2:INDEX(Master!$C:$C,COUNTA(Master!$C:$C))
agnt =Master!$D$2:INDEX(Master!$D:$D,COUNTA(Master!$D:$D))
Now we need to setup the sheet where we need the filtered data to be displayed. Headings were put in cells B5 to B8 and the drop down (using the Data Validation—List) feature was put in cells C5 to C8. Now we need to create or criteria fields in the RawData sheet, this is a requirement and cannot be any place else. When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA and telling Excel where to put the filtered data. I used the cells M1 to P1 to define the headings and cells M2 to P2 to get the actual options from the “Filter Sheet”

Cells Formula
M2 =Filter!C5
N2 =Filter!C6
O2 =Filter!C7
P2 =Filter!C8
Macro to run advanced filter and extract data

Sub FilterData()
Sheets("Filter").Select
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets(“RawData”).Range(“M1:P2”), CopyToRange:=Sheets(“Filter”).Range(“B10”), Unique:=True

Columns.AutoFit
Range(“B10”).Select
End Sub

First we ensure the current filtered data (in any) is cleared out before we run the code again and then we get the new filtered data from cell B10 onwards. Now let’s understand the actual code that filters our data here.


Sheets("RawData").Range("Table1[#All]").AdvancedFilter
Action:=xlFilterCopy,
CriteriaRange:=Sheets("RawData").Range("M1:P2"),
CopyToRange:=Sheets("Filter").Range("B10"),
Unique:=True

We converted our raw data into an excel table (Structured Reference Structured Reference), by doing this we no longer need to know how many rows our data actually goes down to, the “Table1[#All]” takes care of that for us.

We also need to specify that our data is in another sheet and we are trying to run Advanced Filter on that data range, this is done using the first line ” Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter “.

Next we specify the action that we need which is Copy in our case, the other option is “xlFilterInPlace” which would filter right on our data itself.

Then we have specified the Criteria Range (which needs to be on the same sheet where the data is).

And finally we have specified where the output has to be sent to by using : “CopyToRange:=Sheets(“Filter”).Range(“B10″)”

We have also made sure that only Unique records are returned to us by turning Unique:=True.

Kết quả (Việt) 2:

Những dãy tên được tạo ra bằng cách sử dụng hàm INDEX như hình dưới đây Name Range Formula PRD = Master $ A $ 2: INDEX (Master $ A:! $ A, COUNTA (Master $ A: $ A))! RGN = Master $ B $ 2: INDEX (! Thầy $ B: $ B, COUNTA (Master $ B: $ B)) cust = Master $ C $ 2:! INDEX (Master $ C: $ C, COUNTA (Master $ C: $ C ))! agnt = Master $ D $ 2: INDEX (Master $ D: $ D, COUNTA (Master $ D: $ D)) Bây giờ chúng ta cần phải thiết lập các sheet, mà chúng ta cần các dữ liệu đã lọc sẽ được hiển thị. Tiêu đề được đưa vào tế bào B5 đến B8 và thả xuống (bằng cách sử dụng Data Validation-List) tính năng được đưa vào tế bào C5 đến C8. Bây giờ chúng ta cần phải tạo ra hoặc tiêu chí lĩnh vực trong bảng RawData, đây là một yêu cầu và không thể là bất kỳ nơi nào khác. Khi bạn sử dụng hộp thoại Advanced Filter và cố gắng đặt các đầu ra vào một tờ giấy khác Excel sẽ hiển thị một thông báo nói rằng "Bạn chỉ có thể sao chép dữ liệu cho các Sheet Active". Chúng tôi sẽ khắc phục giới hạn này bằng cách sử dụng VBA và nói với Excel nơi để đặt các dữ liệu đã lọc. Tôi đã sử dụng các tế bào M1 đến P1 để xác định các tiêu đề và các tế bào M2 đến P2 để có được sự lựa chọn thực tế từ "Filter Sheet" Cells Formula M2 = Lọc! C5 N2 = Lọc C6! O2 C7 = Lọc! P2 = Lọc C8! Macro để chạy lọc dữ liệu và chiết xuất tiên tiến Sub FilterData () Sheets ("Filter"). Select Range ("B10"). Select Range (Selection, Selection.End (xlToRight)). Select Range (Selection, Hành động: = xlFilterCopy, CriteriaRange: = _ Sheets ("RawData") Range ("M1: P2")., CopyToRange:. = Sheets ("Filter") Range ("B10"), Unique: = True Columns.AutoFit Phạm vi ("B10"). Select End Sub Trước tiên chúng tôi đảm bảo các dữ liệu đã lọc hiện tại (trong bất kỳ) là xóa ra trước khi chúng tôi chạy lại mã và sau đó chúng ta có được những dữ liệu đã lọc mới từ tế bào B10 trở đi. Bây giờ chúng ta hãy hiểu mã thực tế mà các bộ lọc dữ liệu của chúng tôi chuyển đổi dữ liệu liệu của chúng tôi vào một bảng excel (Tham khảo cấu trúc cấu trúc tham khảo), bằng cách làm này, chúng ta không còn cần phải biết làm thế nào nhiều hàng dữ liệu của chúng tôi thực sự đi xuống, các "Table1 [#All]" chăm sóc đó cho chúng tôi. Chúng tôi cũng cần phải xác định rằng dữ liệu của chúng tôi là trong một tờ giấy khác và chúng tôi đang cố gắng chạy Advanced Filter có khoảng từ dữ liệu, điều này được thực hiện bằng cách sử dụng dòng đầu tiên "Sheets (" RawData "). Range (" Table1 [#All] "). AdvancedFilter ". Tiếp theo chúng ta xác định các hành động mà chúng ta cần đó là sao chép trong trường hợp của chúng tôi, các tùy chọn khác là" xlFilterInPlace "mà sẽ lọc ngay trên dữ liệu của chúng tôi riêng của mình. Sau đó, chúng tôi đã xác định các Range (mà cần phải được trên các tờ cùng một tiêu chí . nơi mà dữ liệu) Và cuối cùng chúng tôi đã xác định nơi sản lượng phải được gửi đến bằng cách sử dụng: "). Range (": "CopyToRange = Sheets (" Filter B10 ")" Chúng tôi cũng đã thực hiện chắc chắn rằng chỉ có hồ sơ duy nhất là trả lại cho chúng tôi bằng cách chuyển Unique: = True.