Build a Dynamic Named Range from criteria.

Please see the attached. It uses an event driven macro to repopulate the lists every time there is a change to a cell in columns 1-3.

Comments included.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False 'turns off events so that later lines in the code do not trigger events themselves
For Each Cell In Target  ' As the target range may have more than one cell in it, each cell is treated individually
    If Cell.Column <= 3 Then 'Checks if the change was made in one of the first three columns - only excutes if it is.
        Range(Cells(2, 6), Cells(Rows.Count, 8)).Clear 'Clears the range below the headers in columns 6 - 8
        For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'processes each row in columns 1 - 3 individually
            Cells(Rows.Count, Range(Cells(1, 6), Cells(1, 8)).Find(Cells(N, 3), , xlValues, xlWhole).Column).End(xlUp).Offset(1, 0) = Cells(N, 1) 'looks at the value in column 3 and finds the column header in row 1 which corresponds. Adds the value in column 1 to the next empty cell in the target column.
        Next N
    End If
Next Cell
Application.EnableEvents = True 'turns on events again
End Sub

Other approach

With an pivot table.

First => I don't see in your question, you want to stay away from pivot table.

Second => what's the problem working with pivot table?

In my example you have a dynamic table, which will be used for pivot table.

You only have to refresh the pivot table after adding / changing data.

Thanks for responding and explaining.