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
+1
source to share
4 answers
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
0
source to share
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
+3
source to share