Excel function to create SQL-like queries in worksheet data?

Question

I have a pretty big table on an excel sheet:

Column_1 | Column_2 | Column_3 ValueA ValueB ValueC .... 

I need a function that will take a range and an SQL-like String query as input and return a range of strings matching the query, for example:

 =SQL_SELECT(A1:C1000, "SELECT * WHERE Column_1 = ValueH AND Column_3 = blah") 

Is there something similar? Or what would be the best way to realize yourself?

thank

Answer 1

You can use Get External Data (specify your name) located on the Data tab of Excel 2010 to configure the connection in the workbook to request data from yourself. Use From Other Sources From Microsoft Query to connect to Excel

Once configured, you can use VBA to control the connection to, among other things, view and modify the SQL command that manages the query. This query refers to a memory book, so saving is not required to update the latest data.

Here's a quick Sub to demonstrate access to 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 

Answer 2

If you can save the workbook, then you have the option to use ADO and Jet / ACE to process the workbook as a database and execute SQL on the worksheet.

Information on how to remove Excel using ADO can be found here .

Answer 3

Sometimes SUM_IF can complete a task. Suppose you have a sheet of product information, including a unique product identifier in column A and a unit price in column P. And a list of purchase order records with product identifiers in column A, and you want column T to calculate the unit price for this entry.

The following formula will do the trick in the "Records! T2" cells and can be copied to other cells in the same column.

= SUMIF (Products! $ A $ 2: $ A $ 9999, Entries! $ A2, Products! $ P $ 2: $ 9999)

Then you may have another column with the number of elements for each record and multiply it by the unit price to get the total cost for the record.

Answer 4

If you want to run a formula on a worksheet using a function that executes an SQL statement, then use Add-in A-Tools

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

Answer 5

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