Filter list using a formula (without VBA)

Question

To be clear, this question is not about the Excel terms "filter" or "autofilter". I have a sheet of values that I don't want to alter in any way (sheet1). I have another sheet (sheet2) that I want to create that contains a subset of the rows in sheet1. Is there a way to do this using a formula without leaving any blank rows?

Example below:

   A      | B
 --------------------
1| Person | Age
2| Bob    | 25
3| Mary   | 10
4| Sue    | 15
5| Bill   | 20
6| Sam    | 35
SHEET1 ("People")

   A      | B
 --------------------
1| Person | Age
2| Bob    | 25
3| Bill   | 20
4| Sam    | 35
SHEET2 ("Adults")

I want to enter a formula in Sheet2 that will capture rows in Sheet1 (similar to how a filter would work). This is trivial with blank rows, but I haven't been able to figure out how to do this without blank rows.

PowerQuery is a great tool for this, but you can also just use a humble PivotTable with a Values Filter to only show age > 18 — jeffreyweir

jdphenix jdphenix · Accepted Answer · 2018-04-01T21:17:04

Create a data query to the same Workbook, referencing the sheet with all data.

Ribbon -> get Data -> From File -> From Workbook
Select the same file, then the "People" sheet in the tree view.

Apply the appropriate filter to the resulting query table, using the filtering method of your choice. One option is to filter in the data query directly -

let
    Source = Excel.Workbook(File.Contents("C:\Users\jdphe\OneDrive\Documents\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person", type text}, {"Age", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Age] >= 18)
in
    #"Filtered Rows"

Filter list using a formula (without VBA)

1 Answers