Extracting names from a column to another sheet, based on criteria

Hi there,

I have a column containing names, blank spaces, numbers, a bunch of miscellaneous things. However all I want to do is extract only names, into another sheet, and not the numbers, blank spaces, etc. Youi'll see what I mean when you open the attachment. Offset isn't an option because the names don't appear in the column in an organized fashion. Please help!

Dummy workbook.xlsx

A fast response would be greatly appreciated. Thank you!

First of all you need to correct the entries in cells A13 and A58, where you have a space immediately before the word PAY (to make it consistent with the other entries). Then you can put this formula in B2:

=IF(AND(A2<>"",A5="Travel/inc.Pay:",NOT(ISNUMBER(A2))),MAX(B$1:B1)+1,"-")

and copy this down beyond your entries (e.g. to B70). Then you can use this formula in F1 (I put it there to compare with your results in column E):

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

and copy this down as far as you need to.

Hope this helps.

Pete

If you interested with alternative solution, using Array Formula (press CTRL-SHIFT-ENTER button together), and you can add everything that you need to remove from list including blanks

Sorry for Pete and Ford, you have simple solution there