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

ASP.net MVC DropDownList value used to Create new object

c# asp.net-mvc

This seems to be a pretty trivial problem that I have been stuck on for about an hour, and I don't understand what I'm doing wrong.

PHP Strip string of first url and report results

php string url explode strip

Russel Peter video: <a rel="nofollow" href="http://www.youtube.com/watch?v=2bP9tRhJRTw">www.youtube.com/watch?v=2bP9tRhJRTw</a> russel peters video blah blah. Turtles: <a

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.