Try this event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$F$2" Then
       Application.EnableEvents = False
       Application.ScreenUpdating = False
       columnheaderG = Range("G1").Value
       Range("A:B").Select
       Selection.AutoFilter
       Selection.AutoFilter Field:=1, Criteria1:=Range("F2").Value
       Selection.CurrentRegion.Select
       Range("B:B").SpecialCells(xlCellTypeVisible).Select
       Range("G:G").ClearContents
       Selection.Copy Destination:=Range("G1")
       Selection.AutoFilter
       Range("G1").Value = columnheaderG
       Range("F2").Select
       Application.ScreenUpdating = True
       Application.EnableEvents = True
   End If
End Sub

Additionally you can create a Data Validation list for F2.

Regards,
Stefi

„js” ezt írta:

In column A write this formula:
=IF(B3=$f$3, COUNTIF(B$3:B3, B3), "") drag this formula down.
In column H make a list of 1, 2, 3, 4,............. and so on
In column G type formula:
=IF(ISERROR(VLOOKUP(H3, $A$3:$C$100, 3, 0)), "", VLOOKUP(H3, $A$3:$C$100, 3,
0))
Hide column A and Column H.
this trick can do your work.

Another play ..

In G3:
=IF(ROWS($1:1)>COUNT(H:H),"",INDEX(C:C,SMALL(H:H,ROWS($1:1))))

In H3:
=IF(B3="","",IF(B3=F$3,ROW(),""))
Leave H1:H2 blank

Select G3:H3, copy down to H8, or as far down as required to cover the max
expected extent of source data. Hide away col H. That'll give you the desired
results depending on the input in F3.