Excel function to make SQL-like queries on worksheet data?

You can use Get External Data (dispite its name), located in the 'Data' tab of Excel 2010, to set up a connection in a workbook to query data from itself. Use From Other Sources From Microsoft Query to connect to Excel

Once set up you can use VBA to manipulate the connection to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doen't require a save to refresh the latest data.

Here's a quick Sub to demonstrate accessing the connection objects

Sub DemoConnection()    Dim c As Connections    Dim wb As Workbook    Dim i As Long    Dim strSQL As String    Set wb = ActiveWorkbook    Set c = wb.Connections    For i = 1 To c.Count        ' Reresh the data        c(i).Refresh         ' view the SQL query        strSQL = c(i).ODBCConnection.CommandText        MsgBox strSQL    NextEnd Sub

If you can save the workbook then you have the option to use ADO and Jet/ACE to treat the workbook as a database, and execute SQL against the sheet.

The MSDN information on how to hit Excel using ADO can be found here.

One quick way to do this is to create a column with a formula that evaluates to true for the rows you care about and then filter for the value TRUE in that column.