List of pass names to be extract with formula

[Pinang]

Hi,

can anyone help me to get formula to get pass names from list? attached sample data with result.

Thanks,
Pinang

 

[AlanSidman]

Here is a VBA solution for you

Option Explicit

Sub Pass()
    Dim lr As Long, lrD As Long, c As Range, rng As Range
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("B2" & ":B" & lr)
    For Each c In rng
        lrD = Range("D" & Rows.Count).End(xlUp).Row + 1
        If c = "Pass" Then
            c.Offset(, -1).Copy Range("D" & lrD)
        End If
    Next c
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[p45cal]

In attached:
1. Formula in cell F2
=FILTER(A2:A11,B2:B11="Pass","All Failed")

2. Advanced filter can be used with result in column H, either manually or with a one-line macro.

 

[Pinang]

Thank you for your help and valuable time