Filter data based on values in a list

[mr_hiboy]

Hi,


I have a list of employees in one sheet, one column has a Yes/No value.

I then have another list in sheet two, with the employee name and more info.


Ideally if I select yes in sheet one against and an employee name, I would like to have the second sheet filter only on these employees?


Easy way or Macro or VB?


thanks

Paul

 

[Anant Chirmade]

Can you attach a sample file for reference, So I can provide you with the solution..
Because I cannot explain it completely in text form.

below is my solution :


Sub FilterMacro()

on error resume next

Dim FilList(1 To 100) As String
Worksheets("Shee1").activate

i = 1
Range("B:B").SpecialCells(xlCellTypeConstants).Select
For Each MyCell In Selection.Cells
If MyCell.Value = "Yes" Then
FilList(i) = MyCell.Offset(0, -1).Value

i = i + 1

End If


Next MyCell
ListCount = i

Worksheets("Sheet2").Range("A1:H30000").AutoFilter 2, Array(FilList(1), FilList(2), FilList(3), FilList(4), FilList(5), FilList(6)), xlFilterValues

End Sub


Below are the things considered in the above Macro :

Data is like below table :

Column A Column B
Name Yes/No
Sheefa Shaikh Yes
Fahemida Shaikh No
Imamuddin Khan No
Kunal Dam Yes
Mayur Shirore Yes
Parvinder Kaur Sandhu No
Pratik Shirgaonkar No
Preetam Chettri Yes
Sadaf Shaikh Yes
Salma Shah No



Sheet2 is like this

Column A Column B
Gender Name
Female Sheefa Shaikh
Female Fahemida Shaikh
Male Imamuddin Khan
male Kunal Dam
Male Mayur Shirore
Female Parvinder Kaur Sandhu
Male Pratik Shirgaonkar
Male Preetam Chettri
Male Sadaf Shaikh
Female Salma Shah




However, There is a flaw in this macro...
You will need to add items in Array list if the # of items increases...

 

[SirJB7]

Hi, Anant Chirmade!
Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from the right excluding the last and separated one.
Regards!

 

[Somendra Misra]

Hi mr_hiboy,

See the attached file, I hope I had understood your requirement.

Below is the code in the file:

Sub selectionFilter()
   
    Dim filterCriteria()
    Dim lrG, lrA As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
   
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
   
    Application.ScreenUpdating = False
   
    Range("G2:G1000000").Select
    Selection.Clear
    lrA = Range("A2").End(xlDown).Row
   
    ws1.Range("$A$1:$B$" & lrA).AutoFilter Field:=2, Criteria1:="YES"
    ws1.Range("A2:A" & lrA).Select
    Selection.Copy
    ws1.Range("G2").PasteSpecial (xlPasteValues)
   
   
    ws1.Range("$A$1:$B$" & lrA).AutoFilter Field:=2
   
   

    Application.CutCopyMode = False
    Range("G1").Select
 
    lr = Range("G2").End(xlDown).Row
    ReDim filterCriteria(lr - 1)
    For i = 2 To lr
   
    filterCriteria(i - 2) = Range("G" & i).Value
   
    Next i
   
    ws2.Activate
    Range("A2").Select
    lr1 = Range("A2").End(xlDown).Row
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$" & lr1).AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
   
    Application.ScreenUpdating = True
End Sub

Regards,

 

[Anant Chirmade]

ok Thanks SirJB7.. will do it...

Hi, Anant Chirmade!
Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from the right excluding the last and separated one.
Regards!

 

[Somendra Misra]

@Anant Chirmade

I edited your last comment as you had quoted my comment.

Regards,