Pivot full text instead of counts - Excel

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

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.