Pivot full text instead of counts - Excel

Question

I'm trying to determine the most efficient/effective way to display specific rows from a central table on sheet1 as text on sheet2.

I have setup a table that contains numerous events that is continually being used by multiple people. This sheet acts as a central database and is shared with multiple people who are all using it in real time.

I want to provide a table on sheet2 that allows other users to view specific events from the central database. So I need to export specific values from sheet1 to sheet2. I understand this can easily be done using a filter but the table on sheet1 is constantly being used and it can't be disrupted.

I can't just do a sort or filter on the sheet1 table because it needs to be used by other parties at all times

I really only need to view specific values from sheet1 for the last month. I've got code the exports all rows based off a specific value entered into a designated column on Sheet1. But due to the size of the file Excel constantly crashes.

I then thought a pivot table may be easier and I wouldn't have to use VBA. Is it possible to pivot out specific rows as text, which can be grouped by date, e.g. month?

For instance, if I want to view all ['A's'] and ['X's'] from Column B from the last month as full text it would look like the following:

Central Database table Sheet1

   A   B  C   D
0 11/1 A Big Dog
1 10/1 X  1   2 
2 11/1 Y  Y   Y
3 1/2  A Big Cat
4 1/2  X  3   4 
5 1/2  Y  Y   Y

Output table Sheet2

   A  B  C   D
1 1/2 A Big Cat
2 1/2 X  3   4

Answer 1

As others have mentioned in the comments, using SQL with ADODB is likely a better approach than using PivotTables. I'd also recommend separating your data (Sheet1) from your presentation layer (Excel). E.g. store your data in an actual database like Access, SQL Server etc.

However, as you are looking for a stop gap, I figured I could give you an approach that might fill the need temporarily. The code is commented, but feel free to ask questions. You will need to add a reference to Microsoft Active X Data Object 2.8 or greater to get this working. How to add a reference?

---

Early Binding Approach

Option Explicit
Public Sub DisplayView(StartDate As Date, EndDate As Date)
    'Add a reference to Microsoft Active X Data Object 2.8 or greater
    Dim dbConnection  As ADODB.Connection
    Dim dbRecordset   As ADODB.Recordset
    Dim dbCommand     As ADODB.Command
    Dim OutputSheet   As Excel.Worksheet
    Dim dbField       As Variant
    Dim fieldCounter  As Long

    Set dbConnection = New ADODB.Connection
    Set dbRecordset = New ADODB.Recordset
    Set dbCommand = New ADODB.Command
    Set OutputSheet = ThisWorkbook.Worksheets("Sheet2")

    'Do a quick check to determine the correct connection string
    'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/
    If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"
    Else
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';"
    End If

    'Open the connection and parameterize the query
    dbConnection.Open
    With dbCommand
        .ActiveConnection = dbConnection
        .CommandType = adCmdText
        'A in B in the text below are the field names in your Sheet 1
        'I wasn't sure what the names of the fields are so I named them as they appeared
        'That being Column A is called A, Column B is called B etc
        .CommandText = "Select * from [Sheet1$] where B in ('A','X') and A >= @StartDate and A < @EndDate"
        .Parameters.Append .CreateParameter("@StartDate", adDate, adParamInput, , StartDate)
        .Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, , EndDate)
        Set dbRecordset = .Execute
    End With

    'Clear the Output Sheet
    OutputSheet.Cells.Clear

    'Add Headers to output
    For Each dbField In dbRecordset.Fields
        fieldCounter = fieldCounter + 1
        OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name
    Next

    'Dump the found records
    OutputSheet.Range("A2").CopyFromRecordset dbRecordset
    If dbConnection.State = adStateOpen Then dbConnection.Close
End Sub

'Run from here
Public Sub ExampleRunner()
    'Supply the dates you want to filter for
    DisplayView #1/1/2019#, #1/20/2019#
End Sub

As requested, here is the Late Binding Approach that doesn't require an explicit reference to Microsoft Active X Data Object.

Option Explicit
Private Const adCmdText As Long = 1
Private Const adDate As Long = 7
Private Const adParamInput As Long = 1
private const adStateOpen as long = 1

Public Sub DisplayView(StartDate As Date, EndDate As Date)
    'Add a reference to Microsoft Active X Data Object 2.8 or greater
    Dim dbField       As Variant
    Dim fieldCounter  As Long
    Dim dbConnection  As Object
    Dim dbRecordset   As Object
    Dim dbCommand     As Object
    Dim OutputSheet   As Excel.Worksheet

    Set dbConnection = CreateObject("ADODB.Connection")
    Set dbRecordset = CreateObject("ADODB.Recordset")
    Set dbCommand = CreateObject("ADODB.Command")

    Set OutputSheet = ThisWorkbook.Worksheets("Sheet2")

    'Do a quick check to determine the correct connection string
    'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/
    If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"
    Else
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';"
    End If

    'Open the connection and parameterize the query
    dbConnection.Open
    With dbCommand
        .ActiveConnection = dbConnection
        .CommandType = adCmdText
        'A in B in the text below are the field names in your Sheet 1
        'I wasn't sure what the names of the fields are so I named them as they appeared
        'That being Column A is called A, Column B is called B etc
        .CommandText = "Select * from [Sheet1$] where B in ('A','X') and A >= @StartDate and A < @EndDate"
        .Parameters.Append .CreateParameter("@StartDate", adDate, adParamInput, , StartDate)
        .Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, , EndDate)
        Set dbRecordset = .Execute
    End With

    'Clear the Output Sheet
    OutputSheet.Cells.Clear

    'Add Headers to output
    For Each dbField In dbRecordset.Fields
        fieldCounter = fieldCounter + 1
        OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name
    Next

    'Dump the found records
    OutputSheet.Range("A2").CopyFromRecordset dbRecordset
    If dbConnection.State = adStateOpen Then dbConnection.Close
End Sub

'Run from here
Public Sub ExampleRunner()
    'Supply the dates you want to filter for
    DisplayView #1/1/2019#, #1/20/2019#
End Sub

Answer 2

Here are some screenshots of the Results of the below mentioned Power Query. I selected (In Excel 2003) Data->Import External Data->New Database Query Then, I chose "Excel Files" and added the data I wanted. Make sure to select "is not null" in the query options. I then added the auto sort feature.

You could use a Power Query in the Sheet2 Excel Workbook. Another link here. This way you could update the data whenever you need to. Then, use SQL to query what you need.

It is pretty simple to use and doesn't take any coding (unless you want to use SQL).

This can be done and then in your other workbook you can do the filtering and sorting.

@ryan-wildry 's post is pretty great (and so is his command text for SQL), but if you do not want to use vba or a database then, you can use this (as well as his SQL text).

An example would be: SELECT * FROM [Sheet1$] WHERE Column2='X';

The only problem would be if you have mixed datatypes in the same column. As can be seen with rows 1 & 4 (they were not able to come across with the query because the values are not the same type. (This is using Excel 2003 so if you have a newer version then, you may be able to check it with that).

I've been doing some research and found that Excel's Power Query will handle mixed data types so, you should be set if you are using Power Query.