Filter data from excel table into another table

I'm trying to extract data from one table into another based on filtered criteria.  What I'm really looking for is a way to automatically place the data in another worksheet for reporting purposes.  For example, the source data;

Date       Purchase          Amount
1-1-15     ItemA                   10.00
1-2-15     ItemB                    20.00
2-1-15    ItemC                     15.00
3-1-15     ItemD                   10.00

Given names ReportStart = 1-1-15 and ReportEnd = 1-15-15, I'd like to have 'show up' on another worksheet.  Preference would be to have it appear as a new table, but the priority is to create a structure that could be automatically totaled.  That is, I'd like whenever the first table changes, or the date parameters change, for the reporting data to change, without the user having to take any other actions.  At first glance this seems like a job for a pivot table, but as there's no summation going on, I wasn't sure.

Date       Purchase          Amount
1-1-15     ItemA                   10.00
1-2-15     ItemB                    20.00
Chuck BrownAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"Given names ReportStart = 1-1-15 and ReportEnd = 1-15-15"

How does this result in the two records mentioned at the end of your question?:

"Date       Purchase          Amount
1-1-15     ItemA                   10.00
1-2-15     ItemB                    20.00"

"1-15-15" does not look like a "Date".

Thanks for your clarification.
Chuck BrownAuthor Commented:
Sorry, was just putting in something to give the idea.  Assume it's a properly formatted date.
[ fanpages ]IT Services ConsultantCommented:
OK.

Please look at the attached workbook, containing the Visual Basic for Applications code in the [Filtered Data] worksheet code module, as transposed below:

Option Explicit
Private Sub Worksheet_Activate()

' -------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28698271/Filter-data-from-excel-table-into-another-table.html ]
'
' Question Channel: Experts Exchange > Questions > Filter data from excel table into another table
' Topic Area:       [ http://www.experts-exchange.com/topics/ms-excel/ ]
'
' ID:               Q_28698271
' Question Title:   Filter data from excel table into another table
' Question Dated:   2015-07-18 07:54 PM
' Question Asker:   Chuck Brown
' Asker Profile:    [ http://www.experts-exchange.com/members/clbrownjr.html ]
' Attachment:       (None supplied)
'
' Solution posted:  18 July 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ] | [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                            [ http://NigelLee.info ]
' -------------------------------------------------------------------------------------------------------------------------------

  Dim blnApplication_ScreenUpdating                     As Boolean
  Dim lngErr_Number                                     As Long
  Dim lngRow                                            As Long
  Dim objADODB_Connection                               As Object
  Dim objADODB_Recordset                                As Object
  Dim strSQL                                            As String
  Dim strErr_Description                                As String
 
  On Error GoTo Err_Workbook_SheetActivate
 
  blnApplication_ScreenUpdating = Application.ScreenUpdating
 
  Application.ScreenUpdating = False
  
  Set objADODB_Connection = CreateObject("ADODB.Connection")
 
  objADODB_Connection.Provider = "Microsoft." & IIf(Val(Application.Version) <= 11#, "Jet.OLEDB.4.0", "ACE.OLEDB.12.0")
  objADODB_Connection.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=Excel " & _
                                          IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"
  objADODB_Connection.Open
 
  strSQL = ""
  strSQL = strSQL & "SELECT "
  strSQL = strSQL & "[Date],"
  strSQL = strSQL & "[Purchase],"
  strSQL = strSQL & "[Amount] "
  
  strSQL = strSQL & "FROM "
  strSQL = strSQL & "[EXCEL " & _
                     IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"                                                                            ' Note: Val(...) only recognizes a period ["."] as a valid decimal separator
  strSQL = strSQL & "IMEX=1;"
  strSQL = strSQL & "HDR=Yes;"
  strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Source Data$]"
 
  strSQL = strSQL & "WHERE "
  strSQL = strSQL & "[Date]=#1-Jan-2015#"
  strSQL = strSQL & "OR "
  strSQL = strSQL & "[Date]=#1-Feb-2015#"
  
  Set objADODB_Recordset = CreateObject("ADODB.Recordset")
 
  objADODB_Recordset.CursorType = 3                                                                                                                     ' adOpenStatic
  objADODB_Recordset.CursorLocation = 3                                                                                                                 ' adUseClient
  objADODB_Recordset.ActiveConnection = objADODB_Connection
 
  objADODB_Recordset.Open (strSQL)
 
  ActiveSheet.Cells.ClearContents
  ActiveSheet.Cells.ClearFormats
  
  Worksheets("Source Data").Rows(1&).Copy ActiveSheet.Rows(1&)
  
  ActiveSheet.[A2].CopyFromRecordset objADODB_Recordset
  
  lngRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row + 1&
  
  If lngRow <= ActiveSheet.Rows.Count Then
     ActiveSheet.Cells(lngRow, 1) = "TOTAL"
     ActiveSheet.Cells(lngRow, 3).Formula = "=SUM(" & ActiveSheet.Range(ActiveSheet.Cells(2&, 3), ActiveSheet.Cells(lngRow - 1&, 3)).Address & ")"
     
     ActiveSheet.Rows(lngRow).Font.Bold = True
     
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeTop).LineStyle = xlContinuous
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeTop).ColorIndex = xlAutomatic
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeTop).Weight = xlThin
     
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeBottom).LineStyle = xlContinuous
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeBottom).ColorIndex = xlAutomatic
     ActiveSheet.Range(ActiveSheet.Cells(lngRow, 1), ActiveSheet.Cells(lngRow, 3)).Borders(xlEdgeBottom).Weight = xlThin
     
     ActiveSheet.Cells(lngRow, 3).Select
  End If ' If lngRow <= ActiveSheet.Rows.Count Then
 
  ActiveSheet.Columns.AutoFit
  
Exit_Workbook_SheetActivate:

  On Error Resume Next
 
  If Not (objADODB_Recordset Is Nothing) Then
     objADODB_Recordset.Close
     Set objADODB_Recordset = Nothing
  End If ' If Not (objADODB_Recordset Is Nothing) Then
 
  If Not (objADODB_Connection Is Nothing) Then
     objADODB_Connection.Close
     Set objADODB_Connection = Nothing
  End If ' If Not (objADODB_Connection Is Nothing) Then

  Application.ScreenUpdating = blnApplication_ScreenUpdating
  
  Exit Sub
 
Err_Workbook_SheetActivate:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  Application.ScreenUpdating = True
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
          vbCrLf & vbLf & _
          strErr_Description, _
          vbExclamation Or vbOKOnly, _
          ActiveWorkbook.Name
         
  Resume Exit_Workbook_SheetActivate
 
End Sub

Open in new window



The columns ([Date], [Purchase], &/or [Amount]) within the [Source Data] worksheet may be changed, or rows added/removed (as long as the first row with the column headings remains), & when the [Filtered Data] worksheet is selected (activated) the (revised) [Source Data] contents are collated based on the filter:

  strSQL = strSQL & "WHERE "
  strSQL = strSQL & "[Date]=#1-Jan-2015#"
  strSQL = strSQL & "OR "
  strSQL = strSQL & "[Date]=#1-Feb-2015#"
Q-28698271.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Chuck BrownAuthor Commented:
I have a couple of clarification questions as I'm setting up the VBA.

1. I have two names set up BudgetDateStart and BudgetDateEnd.  How do I refer to those names in the VBA so that I can say something like (I'm guessing this isn't the right syntax):

  strSQL = strSQL & "[Date]=[@BudgetDateStart]"
  strSQL = strSQL & "AND "
  strSQL = strSQL & "[Date]=[@BudgetDateStart]"

2. Do I need to modify this line to reflect the source?
  strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Source Data$]"

3. Do I need to modify this line?
  Worksheets("Source Data").Rows(1&).Copy ActiveSheet.Rows(1&)

4. Where (if not the above) do I specify the destination?

Thank you!
[ fanpages ]IT Services ConsultantCommented:
1. Do you mean a named range in MS-Excel, or a variable (or constant) within your Visual Basic for Applications code?

2. This line indicates the active workbook (where the code resides) also hosts the [Source Data] worksheet.

If it is feasible that the ActiveWorkbook is not necessarily the workbook where the code is executing, please change the line to:

strSQL = strSQL & "DATABASE=" & ThisWorkbook.FullName & "].[Source Data$]"

There is no need to change this if the workbook containing the code is always going to be the workbook where the [Source Data] worksheet (or whatever you wish to name it) is also found.

3. This line copies the column headings (on row 1) from the [Source Data] worksheet to the destination (active) worksheet; in this case, [Filtered Data].

4. The destination of the copied range (row 1 of [Source Data])?  That is within the statement you lifted & posted above:

Worksheets("Source Data").Rows(1&).Copy ActiveSheet.Rows(1&)
Chuck BrownAuthor Commented:
1. Named Range
2. The code will always be in the same workbook.  I wasn't sure about Source Data because of the "$".  Just so I'm clear, if code is in the same workbook, and my data is in worksheet "Register", this line should appear as:
  strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Register]", correct?
3. Sorry to make this complicated, but if my destination worksheet already has other data on it, and I want this extracted table to be at the end of any data that's already there, is that easily accomplishable?  What I'm doing is taking data from several sheets and pulling different values to create a single summary sheet.
4. Sorry, but I'm not following you; where in this statement "Worksheets("Source Data").Rows(1&).Copy ActiveSheet.Rows(1&)" am I providing the name of the destination worksheet?

Also, where should this code be installed; in one of the "sheets" pages or the "Workbook" page?

Thanks
[ fanpages ]IT Services ConsultantCommented:
1. If the named range is "BudgetDateStart" the code will change from

  strSQL = strSQL & "[Date]=#1-Jan-2015#"
  strSQL = strSQL & "OR "
  strSQL = strSQL & "[Date]=#1-Feb-2015#"

...to the single line...

  strSQL = strSQL & "[Date]=#" & Format$(Range("BudgetDateStart"), "dd-Mmm-yyyy") & "#"

(There is no need for the second, 'OR' clause if only one date is required)


2. No, not correct, but close.  The suffix of "$" is still required:

 strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Register$]"

That is necessary to indicate the end of the worksheet name.


3.  Yes, you will just have to locate the last (used) row of the existing data & change the code to begin pasting the new data at that location.

First of all, remove these two lines:

  ActiveSheet.Cells.ClearContents
  ActiveSheet.Cells.ClearFormats

(As you do not wish to clear the existing [active] worksheet contents)

My code pastes the summarised data at the (topleft) cell [A2]:
 
  ActiveSheet.[A2].CopyFromRecordset objADODB_Recordset

You can use an approach like I did with the additional "TOTAL" row, by changing that one line (above) to these two:

  lngRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row + 1&

  ActiveSheet.Cells(lngRow, "A").CopyFromRecordset objADODB_Recordset

Now the newly summarised data will be pasted on the next row down from the last row of the existing worksheet data.

(You may also wish to remove the "TOTAL" row from my original code, if this is not needed between each successive paste of data).


4. Within the code I posted, the destination worksheet is (always) the "ActiveSheet", as the code is executed when the destination worksheet has been activated (selected by clicking the named "tab" in the MS-Excel interface).

So...

Worksheets("Source Data").Rows(1&).Copy ActiveSheet.Rows(1&)

ActiveSheet refers to where the first row of the [Source Data] worksheet is to be pasted.


Finally,

You can place the code where you like; the code module of a worksheet, the code module of the workbook, or a (dedicated) Public code module.

The location may depend on how you invoking the code; when it is intended to execute.

Just to reiterate; presently, the code above is executed when the destination worksheet is activated, hence it is in the code module of that worksheet.


If you have different requirements, perhaps it would be wise to being a new question, referencing this one (so that other 'Experts' may get involved, if they wish to), & relay the format/presentation & nature of your intended run-time workbook.

My responses here have been based on the original question you asked, without any emphasis on the usage within a different scenario (within an existing workbook).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.