Excel function to make SQL-like queries on worksheet data

Question

I have a largish table in an Excel worksheet   Column 1   Column 2   Column 3  ValueA       ValueB     ValueC        What I need is a function that will take as input the range and an SQL-like query String and return a range of rows that match the query  e g     SQL SELECT A1 C1000   SELECT   WHERE Column 1   ValueH AND Column 3   blah     Does something like this exist  Or what would be the best way to implement myself

User · Answer

Sometimes SUM IF can get the job done    Suppose you have a sheet of product information  including unique productID in column A and unit price in column P  And a sheet of purchase order entries with product IDs in column A  and you want column T to calculate the unit price for the entry   The following formula will do the trick in cell Entries T2 and can be copied to the other cells in the same column    SUMIF Products  A 2  A 9999 Entries  A2  Products  P 2  9999    Then you could have another column with number of items per entry and multiply it with the unit price to get total cost for the entry

User · Answer

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

User · Answer

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

User · Answer

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     Next End Sub

User · Answer

If you want run formula on worksheet by function that execute SQL statement then use Add-in A-Tools  Example  function BS SQL  SELECT

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

Examples related to sql

Examples related to excel

Examples related to vba

Examples related to function