Returning list with common values

[Topdrop18]

Hello All,

I have searched through the posts and could not find an answer to this one. I am trying to search through data and return all of the results that have a common value. For simplicity, say I have a list of 5 people in column A (John, Mike, Jeff, Andrew, Simon) and their job title in column B (President, Analyst, CFO, Manager, Analyst). I am looking for a formula that will return all of the people in column A, depending upon my search criteria in column B. So, if I put in "Analyst", I will get a result of Mike and Simon. I know I could do a vlookup and have the range change time but because my data set is much larger than my example, that is not feasible. Thank you for some help.

Jeff

 

[cb12]

You could try something simple like the following, unless your situation is more complex than you first stated. If it is (and perhaps even if its not) you should consider importing your data into a Access table and running a simple select query.

Obviously manipulate the code as per your requirements.

Sub test()
Dim JobTitle As String
Dim i, j, k As Integer

    Cells(1, 1).Select
    i = Selection.CurrentRegion.Rows.Count
    JobTitle = Cells(1, 4).Value
    k = 2
    
    Cells(1, 2).Select

    For j = 1 To i
        If ActiveCell.Value = JobTitle Then
            Cells(k, 4).Value = ActiveCell.Offset(0, -1).Value
            ActiveCell.Offset(1, 0).Select
            k = k + 1
        Else: ActiveCell.Offset(1, 0).Select
        End If
    Next j
End Sub