Displaying in one cell a number from a column in a table that has duplicates

welcome to the forum dconsole2

is this anything like what you want?
- it creates 3 tables from one source table - one each for X,Y and Z

in the attached workbook...
- the Table of data is in sheet Data
- go to sheet X
- to create table for X double click on any cell in that sheet
- same for Y and Z

this code is in a general module:

Sub CreateTable(ws)
    
    Dim Data As Worksheet, LastRow As Long
    Dim DataCel As Range, rng As Range, wsCel As Range

    Set Data = Sheets("Data")
    Set DataCel = Data.Range("A1")
    LastRow = Data.Range("A" & Cells.Rows.Count).End(xlUp).Row
    Set rng = DataCel.Resize(LastRow, 6)
    
    Set wsCel = Sheets(ws).Range("A1")
    rng.AutoFilter Field:=1, Criteria1:=ws
    rng.SpecialCells(xlCellTypeVisible).Copy
    wsCel.PasteSpecial (xlPasteAll)
    wsCel.PasteSpecial (xlPasteValues)
    
'put sheet Data back to neutral
    Data.AutoFilterMode = False
    Application.CutCopyMode = False
    Data.Activate
    Data.Range("A1").Select
End Sub

the above code is called by this in sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call CreateTable(Me.Name)
    Cancel = True
    Me.Activate
    Range("A1").Select
End Sub

If it isn't what you want....
- please attach a workbook showing the original table and mocking up on other sheets what you want the output to look like for X,Y & Z

ManageAttachments3.jpg