Extract a list from master list

[js]

Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange

 

[Stefi]

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:

 

[Excel_Learner]

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.

 

[Max]

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.

 

[Excel_Learner]

In column A write this formula:
=IF(B3=$f$3, COUNTIF(B$3:B3, B3), "") drag this formula down.
In column G type formula:
=IF(ISERROR(VLOOKUP(row(1:1), $A$3:$C$100, 3, 0)), "", VLOOKUP(row(1:1),
$A$3:$C$100, 3, 0))
Hide column A.
Let us know if it works.