How Do I Implement Excel Filter Function Through Formula?

Question

I have a table of data, and some of the data is garbage. There's about 30000 entries sorted by unit. I only care about 20 of those units. I can easily sort the data by applying a filter, but it's tedious to click the 23 or so times and I'm going to have to manage this report weekly.

I've captured the relevant criteria into a different sheet, all non-repeating values sorted into a column. I'd like to arrange another sheet so that it only displays the rows from my table if the data in their unit column matches the criteria column.

I know I need to use VLOOKUP... somehow, but I haven't stumbled across any tutorials that compare a cell's value to a table.

In case that was all very confusing:

My table:

Action | Job Desc    | Dept         
XFR    | IT Guy      | Home Office 1
POS    | Security Guy| Satellite Office
TTL    | Analyst Guy | Home Office 2

I want to have a new sheet that only contains 3 rows:

Action | Job Desc    | Dept         
XFR    | IT Guy      | Home Office 1
TTL    | Analyst Guy | Home Office 2

I have the values "Home Office 1" and "Home Office 2" stored elsewhere (there are actually 28 different office values). How do I build this sheet so it only displays these values - similar to the stock Excel filter function?

Answer 1

Here is my anser...

Sub takeMyValus()
    Dim r1
    Dim r2
    Dim c
    Dim rng1 As Range
    Dim rng2 As Range
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim sht3 As Worksheet
    Dim List()
    Dim i
    Dim j

    r1 = Range("A1").End(xlDown).Row 'to know the last row
    c = Range("A1").End(xlToRight).Column 'to know the last colum
    Set sht1 = Sheets("Data") 'this is the name I used, but you
                              'put the name of your data sheet
    Set sht2 = Sheets("List") 'the sheet with the sorted list of the data you want


    sht1.Activate 'Just in case
    Set rng1 = Range(Cells(1, 1), Cells(r1, c)) 'set just the range with data
    rng1.AutoFilter 'set the autofilter
                    'is better if the data has no autofilter
                    'when you begin to run the macro

    sht2.Activate
    'imagine that you got the list in column A and is just 5 items in your data
    'With no header
    '+---------+
    '| Office3 |
    '| Home5   |
    '| Office8 |
    '| Home8   |
    '| Sat2    |
    '+---------+
    'List for my example...


    r2 = Range("A1").End(xlDown).Row 'to know the total item on the list
                                     'in this case will be 5

    Set rng2 = Range(Cells(1, 1), Cells(r2, 1)) 'set the range of the list
                                                'that is Range("A1:A5")
    j = 0 'ini the counter

    For Each i In rng2 'for every cell in Range("A1:A5")
        j = j + 1 'increase the j to 1 every time
        ReDim Preserve List(1 To j)
        'redimension the var...
        List(j) = i.Value 'store every cell (just the data) into an array
    Next i 'next one.

    sht1.Activate 'go to sheet with all the data
    rng1.AutoFilter Field:=3, Criteria1:=Array(List), Operator:=xlFilterValues 'set the filter with the list
    rng1.SpecialCells(xlCellTypeVisible).Copy 'copy just the cells that you can see, this is the filter

    Sheets.Add after:=Sheets(Sheets.Count) 'add a new sheet
    ActiveSheet.Name = myTime 'put a diferent name, see the function below
    Set sht3 = ActiveSheet 'store the new sheet into this var

    sht3.Activate 'go to the new sheet... is already activate, but just in case...
    Range("A1").PasteSpecial xlPasteAll 'paste all in Range("A1")
    Application.CutCopyMode = False 'is like press ESCAPE in the keyboard

End Sub

Function myTime() As String 'the function a told you
    Dim HH
    Dim MM
    Dim SS
    Dim TT
    HH = Hour(Now)
    MM = Minute(Now)
    SS = Second(Now)
    myTime = Format(HH, "00") & Format(MM, "00") & Format(SS, "00")
End Function

Here is the example of my data...

+--------+---------+----------+
| Action | Job Des |   Dept   |
+--------+---------+----------+
| XFR    | IT      | Office1  |
| POS    | Sec     | Office2  |
| TTL    | Analyst | Office3  |
| XFR    | IT      | Office4  |
| POS    | Sec     | Office5  |
| TTL    | Analyst | Office6  |
| XFR    | IT      | Office7  |
| POS    | Sec     | Office8  |
| TTL    | Analyst | Office9  |
| XFR    | IT      | Office10 |
| POS    | Sec     | Home1    |
| TTL    | Analyst | Home2    |
| XFR    | IT      | Home3    |
| POS    | Sec     | Home4    |
| TTL    | Analyst | Home5    |
| XFR    | IT      | Home6    |
| POS    | Sec     | Home7    |
| TTL    | Analyst | Home8    |
| XFR    | IT      | Home9    |
| POS    | Sec     | Home10   |
| TTL    | Analyst | Home11   |
| XFR    | IT      | Home12   |
| POS    | Sec     | Sat1     |
| TTL    | Analyst | Sat2     |
| XFR    | IT      | Sat3     |
| POS    | Sec     | Sat4     |
| TTL    | Analyst | Sat5     |
| XFR    | IT      | Sat6     |
| POS    | Sec     | Sat7     |
| TTL    | Analyst | Sat8     |
| XFR    | IT      | Sat9     |
| POS    | Sec     | Sat10    |
| TTL    | Analyst | Sat11    |
| XFR    | IT      | Sat12    |
| POS    | Sec     | Sat13    |
| TTL    | Analyst | Sat14    |
+--------+---------+----------+

The list

+---------+
| Office3 |
| Home5   |
| Office8 |
| Home8   |
| Sat2    |
+---------+

The result:

+--------+---------+---------+
| Action | Job Des |  Dept   |
+--------+---------+---------+
| TTL    | Analyst | Office3 |
| POS    | Sec     | Office8 |
| TTL    | Analyst | Home5   |
| TTL    | Analyst | Home8   |
| TTL    | Analyst | Sat2    |
+--------+---------+---------+

Answer 2

A very common question.

Assuming that:

1) You are using Excel 2010 or later

2) The original table is in Sheet1!A1:C10 (with headers in row 1)

3) The table to house the (filtered) results is in Sheet2 and of an identical layout to the original table

4) The list of (28) criteria is in Sheet3!A2:A29

then enter this single formula in Sheet2!J1:

=SUMPRODUCT(COUNTIF(Sheet3!A2:A29,Sheet1!C2:C10))

Of course, the choice of cell here does not have to be J1, though, whatever you choose, make sure that it is a cell which is external to your results table. This formula is a one-off, and, unlike those in the main results table, NOT designed to be copied to any further cells; it simply determines the number of expected returns, and will be referenced in the main table formulas, thus avoiding resource-heavy IFERROR set-ups.

The formula in cell A2 of the results table is then:

=IF(ROWS($1:1)>$J$1,"",INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!B$2:B$10)/MATCH(Sheet1!$C$2:$C$10,Sheet3!$A$2:$A$29,0)^0,ROWS($1:1))))

and copied down and to the right as required.

Obviously if the upper row reference in your original table is not actually 10 then you will need to amend that part within these formulas accordingly. However, be sure not to choose an arbitrarily large value, since, for each additional cell referenced, extra calculation will be required (and that applies whether those additional cells are technically beyond the last-used cells in those ranges or not.)

As such, I recommend that you either choose a suitably low, though sufficient, upper bound for the end row being referenced or, even better, make your ranges dynamic, such that they automatically adjust as your data expands/contracts.

Regards

Answer 3

I think the easiest way to do this is by creating a tab of "interesting" units and vlookup to this. In the new interesting tab you would list the 20 items you are interested in column A.

In the data tab with all 30,000 rows you need to add a new column to check each row if it exists in the interesting tab. I assume the units are in column C and you are entering this formula in cell D1 =NOT(ISERROR(VLOOKUP(C1,InterestingTab!A:A,1,0))).

The result of the formula is TRUE or FALSE, which can easily be filtered on. Then you can easily add new items to the interesting tab and it will update automatically.