Create a smaller list from data in a larger list [

Question

I need some help in creating a list using information that is contained in a larger list in Excel. For instance, in cells A1:A15 I have a persons name. In cells B1:B15 I have a drop down box indicating Pass/Fail. I am only interested in the people who have failed, so is there a way to create a smaller list that will contain their name based on if they have the Fail designation in cells B1:15? Thanks in advance for any help.

Answer 1

Use this formula, say in E2:

=INDEX($A$1:$A$15,SMALL(IF($B$1:$B$15="Fail",ROW($B$1:$B$15)),ROW()-ROW($E$2)+1))

press CTRL+SHIFT+ENTER to evaluate it and drag it down.

Another way would be to filter column B for Fail values and copy only visible rows

Answer 2

Try this small macro:

Sub SmallerList()
    K = 1
    For Each r In Range("B1:B15")
        If r.Value = "Fail" Then
            Range("C" & K).Value = r.Offset(0, -1).Value
            K = K + 1
        End If
    Next r
End Sub