SQL-WHERE TYPE Formulas in Excel

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

Answers


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

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.


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


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


Need Your Help

What is a good scripting language for basic file operations?

file scripting cygwin scripting-language

I often need to copy, move or rename files and directories based on some criteria as I collect data from many resources and I like to keep things organized. What scripting language provides the most

Django fixtures: Cannot assign data to textfield using json file

python mysql django json

I am trying to load data into mysql database using Django's "manage.py loaddata" command. When I try to load the file containing following json content, I get an error