SQL-WHERE TYPE Formulas in Excel

Question

Let say I have a sheet in with columns Customer and CreatedDate with lots of row with data. Anyone who knows how to setup (through VBA or Formula) a second sheet that displays rows from the first sheet based on certain where statements, i.e. all rows with customers "created this month." (similar to a select ... where query against a SQL database).

Thanks! /Niels

Answer 1

You can create a Pivot Table out of your data, then slice-n-dice it lots of ways.

Answer 2

There isn't an exact equivalent to the SQL select ... where functionality in Excel, but take a look at the VLOOKUP function. It may be what you are looking for. If that doesn't have enough functionality, you will probably have to use VBA:

Dim DataRange as Range
Dim RowNum as Integer
Dim NewRow as Integer
Dim TestMonth as Integer
Dim ThisMonth as Integer

Set DataRange = Range(Sheet1.Cells(1,1), Sheet1.Cells(100,2))

ThisMonth = Application.WorksheetFunction.Month(Application.WorksheetFunction.Today())

NewRow = 1
For RowNum from 1 to DataRange.Rows.Count
    TestMonth = Application.WorksheetFunction.Month(DataRange.Cells(RowNum, 1).Value)
    if TestMonth = ThisMonth Then
        Sheet2.Cells(NewRow, 1).Value = DataRange.Cells(RowNum, 2).Value
        NewRow = NewRow + 1
    End If
Next RowNum

Answer 3

You could create a copy of the main data sheet (either by copying the sheet, or using a =Sheet1!A1 type formula filled across and down), and then use an Autofilter to narrow it down to the rows you require.

Answer 4

Here is an example using ADO.

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$] WHERE CourseKey=484"

rs.Open strSQL, cn

Worksheets(2).Cells(2, 1).CopyFromRecordset rs

Further information: http://support.microsoft.com/kb/246335