vlookup or something simliar

[blacktour]

I am trying to use a formula that will do this:

Data ranges from A1 to D50

I want to look for a word that is spreadout in the C column lets say "WORK"

  A             B           C            D
1words     more                  words
2words     more       WORK     words
3          more    WORK     words
4words     more                  words
5words     more    WORK     words
6words     more                  words

So for any row in column C that says "WORK" I want to extract it out and list it on another work sheet

so it ends up looking like this:

words     more    WORK     words
words     more    WORK     words
words     more    WORK     words

Hopefully thats not too confusing and can be done using a formula?

Thanks for your help,
Mike

 

[Paddy1979]

Sorry think a macro is the only solution

Paste the below code in the macro window ( Alt F8)

Sub copy_data()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).row
n = 1

'any text 'Work' in column 3 (C) the whole row will
' be copied over to Sheet2

For i = 1 To lr
If Cells(i, 3).Text = "Work" Then
  Rows(i).Copy
   Worksheets("Sheet2").Rows(n).PasteSpecial
  n = n + 1
 End If
Next i

Application.CutCopyMode = False

End Sub