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?

If you can save the worksheet then you have the option to use ADO and Jet/ACE to treat the workbook as a database, and execute SQL against the sheet. — Jon Egerton, Jan 06 '12 at 11:01
[pivot tables](http://stackoverflow.com/search?q=[excel]+pivot+table&submit=search)? — SeanC, Nov 12 '12 at 18:17
Stumbled across this due to a recent "late answer". Surprised no-one seems to have mentioned Excel's "Advanced Filter" functionality, which is at least in the right ball-park here. — Steve Lovell, Jun 03 '17 at 14:50

score 50 · Accepted Answer · edited Mar 11 '22 at 15:14

50

You can use Get External Data (despite 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 doesn'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

edited Mar 11 '22 at 15:14

Lee Taylor

7,761
16
33
49

answered Jan 07 '12 at 01:21

chris neilsen

52,446
10
84
123

Thanks for this, looks to be just what I am looking for – Richard H Feb 09 '12 at 13:58
3

could you tick that this answered your question Richard? – K_B Nov 29 '12 at 11:48
1

I get an "object doesn't support this action" error at the strSQL = c(i).odbcconnection.commandtext line – zoonosis Aug 02 '13 at 05:28
1

@JustinJDavies - these days i am behind a corporate firewall so SO access irregular. Now accepted. – Richard H Oct 09 '14 at 20:36
1

Sorry to hear that Richard! Sounds like a sorry state of affairs if you are fire-walled from such a useful resource. – JustinJDavies Oct 09 '14 at 20:38
Hi, guys! Is there a way to get it to auto-refresh the data returned, making it dynamic? (Just like the "QUERY" function in Google Spreadsheet). I kow I could use VBA to do that - just want to know if I can get it working without VBA. – Oneide Apr 04 '15 at 02:31
how do you perform this on a mac? Microsoft query is not showing up – Brandon Oct 05 '21 at 21:26

score 5 · Answer 2 · answered Jan 06 '12 at 11:04

5

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.

answered Jan 06 '12 at 11:04

Jon Egerton

40,401
11
97
129

Hmm that is certainly a possibility, thanks for the suggestion but would prefer not to have to reference "externally" as I would essentially be doing here – Richard H Jan 06 '12 at 11:11
What do you mean by "externally"? – Jon Egerton Jan 06 '12 at 11:12
1

well ADO/Jet references a shreadsheet on the filesystem (which also means the sheet needs to be saved) as opposed to the in-memory range. – Richard H Jan 06 '12 at 11:56

score 2 · Answer 3 · answered Mar 17 '15 at 16:47

2

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.

answered Mar 17 '15 at 16:47

user4681810

21
1

score 2 · Answer 4 · edited Jun 04 '17 at 01:19

2

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

Example, function BS_SQL("SELECT ..."):

edited Jun 04 '17 at 01:19

Daniel

2,355
9
23
30

answered Jun 03 '17 at 14:09

Nguyễn Duy Tuân

21
2

score 1 · Answer 5 · edited Jul 05 '18 at 11:33

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.

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

5 Answers5

Linked