Copy rows in sheet if certain cell contains text

Hi, basically I have a sheet that contains a number of rows with text in, the end cell on each row does not always contain some text. So what I'm looking to do is for the cells that do contain text, copy that entire row into another sheet?
How would I go about doing this?

You can do it with a few formulae - attach a sample workbook and I can show you how.

Pete

Hi Pete, I've attached a basic spreadsheet where on the end cells some are empty as explained

I've used column K as a helper column in both sheets (shown in blue), with this formula in K2 of Sheet1:

Formula:

Please Login or Register  to view this content.

This gives an increasing sequential count of the records which meet the criteria when copied down the column. I've copied to row 15 (i.e. beyond your data) - the hyphens help to show how far it has been copied. I've used this formula in K2 of Sheet2:

Formula:

Please Login or Register  to view this content.

which tells us the rows on Sheet1 where those sequential numbers occur. In A2 of Sheet2 I have used this formula:

Formula:

Please Login or Register  to view this content.

which retrieves the data from column A of Sheet1 where there is a row number in column K. This formula can be copied across into B2:I2 to retrieve data from the corresponding columns in Sheet1. Then all the formulae on row 2 can be copied down as far as you need - again, I've copied to row 15, as shown by the hyphens in column K.

So, in Sheet2 you have all the records from Sheet1 where column I contains "Available".

Hope this helps.

Pete

That looks ideal, the only issue that I forgot to mention is that the text where it says available may not always have that specific phrase it may be a number of other entries, how does this affect the formulae?

You can change the first formula in Sheet1 to this:

=IF(I2="","-",MAX(K$1:K1)+1)

then copy down. You will get the numbers (and thus those records will be copied into Sheet2) if there is any text in column I.

Hope this helps.

Pete

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete