SQL-WHERE TYPE Excel Formulas
Let's say I have a sheet with Customer and CreatedDate columns with many rows of data. Anyone who knows how to set up (via VBA or Formula) a second sheet that displays rows from the first sheet based on certain operators i.e. All rows with customers "created this month". (similar to query ... where query is against SQL database).
Thanks! / Niels
There is no exact equivalent to SQL functionality in Excel select ... where
, but look at the function VLOOKUP
. Perhaps this is what you are looking for. If that's not enough, you 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 can create a copy of the master data sheet (either by copying the sheet or by using a type formula =Sheet1!A1
filled across and down) and then use Autofilter to narrow it down to the rows you want.
Here's 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
More information: http://support.microsoft.com/kb/246335
You can create a pivot table from your data and then n-dice slicing in many ways.