List Only

Excel List Question?

I have information being pulled into Excel using a web query. The result looks like this: (A1) Adam (A2) Brad (A3) Chris (A4) OFFICER (A5) Dave (A6) Fred (A7) OFFICER (A8) Greg ...and so on for a thousand lines or so. Basically, it's a list of names, and if someone is an officer it says so in a cell beneath their name. Is there a way I can get a list of all of the officers? It is a list that changes daily, so I need it to be able to update with the excel query. The closest I could figure out was an index/match of (using the above format): =INDEX(A1:A8,MATCH ("Officer",A1:A8,FALSE) -1,1) However, to find the second one, the formula has to automatically adjust the range to A5:A8. I'm not sure how to do that. Any advice on how to make this work? Thanks in advance.

Public Comments

1. Say your list of names is in A1:A8, you want the list of officer's names, use the following array formula This formula has to place in the 1st row of the name list, in this case is row 1 =INDIRECT( "A"&SMALL( IF(A$1:A$8= "OFFICER", ROW(A$1:A$8)), ROW()-ROW(E$1 )+1)-1) In order to have result, it's needed to change above formula to array formula, copy the above formula to a cell, while the cell is selected hit F2 then hold down Shift and ctrl then hit enter, it will activate the formula to array formula, on the formula bar, curly brackets { } will enclosed it. {=INDIRECT( "A"&SMALL( IF(A$1:A$8= "OFFICER", ROW(A$1:A$8)), ROW()-ROW(E$1 )+1)-1)} To get subsequent officer's names, copy the cell down until you got #NUM! in the cell Please contact for more info.
2. Here is a not so elegant macro that will evaluate rows A1 to A5000, insert the names of all officers in column B, and sort them alphabetically. Sub findOfficers() Dim rng As Range Dim Ofcr As String Set rng = Range("A1:A5000") Application.ScreenUpdating = False Range("B:B").Clear For Each cell In rng cell.Select If ActiveCell.Value = "officer" Or ActiveCell.Value = "Officer" Then ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 0).Value End If Next Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B1").Value = "Officers" Range("A1").Select Application.ScreenUpdating = True End Sub Copy the macro to the clipboard. Press ALT + F11 Insert > Module Paste the macro in the Module area to the right. Close back to Excel Go to Tools > Macro > Macros Highlight the 'findOfficers' macro, if it is not already highlighted. Click: Options Select a letter to be used as a keyboard shortcut. Choose something close to the Ctrl key for ease of activation. Close back to Excel To run the macro press Ctrl + your shortcut letter.