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?
The answer is
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.
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.
If you want run formula on worksheet by function that execute SQL statement then use Add-in A-Tools
Example, function BS_SQL("SELECT ..."):
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.
Source: Stackoverflow.com