Find rows that contain specific text

[Cremorneguy]

Hi there

I have a spreadsheet containing multiple data across rows. I only want items to appear based on certain text from one column, I want it to appear in chronological order however. This spreadsheet will be used by various people with data being entered daily, so the simple filter option won't work with what I want it to do.

Basically, this is what it currently shows:

Example A Subject A Open
Example B Subject B Open
Example C Subject C Closed
Example D Subject D In Progress
Example E Subject E Closed
Example F Subject F Open

Basically, I only want items that have either Open or In Progress status to appear, so using the above examples, the result should show:

Example A Subject A Open
Example B Subject B Open
Example D Subject D In Progress
Example F Subject F Open

So it stays in alphabetical order, but anything where the status shows Closed does not appear.

Is there a way to do this without the excel file going really slow, please let me know if I might need a macro to do this and I will post this in the VBA forum.

Thanks

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jacc]

Somthing like this?

[Glenn Kennedy]

An alternative (no helper and non-array)

=IFERROR(INDEX(A:A,SMALL(INDEX(($C$2:$C$7="Closed")*10^10+ROW($C$2:$C$7),0),ROWS($1:1))),"") drag across 3 columns for Header 1, 2 & 3 and down as far as needed. Adjust the ranges for C as needed.

[Aardigspook]

Hi,

You can use the following macro to hide any Closed lines:

Please Login or Register  to view this content.

Replace Range("B1:B" with the column you need to search for 'Closed'.

If you want to run this manually, just put it in a normal module.
If you want it to run automatically when the file is opened, put it all in 'ThisWorkbook' preceded by this:

Please Login or Register  to view this content.

The attached file should show this working.
HideRowIfTextFound.xls

Please note, this is not all my work - the main macro is modified from code found here:
http://answers.microsoft.com/en-us/o...0-28e74578f0c8

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[Cremorneguy]

Thank you to everyone that assisted on this. Glenn, yours was spot on, the exact thing I wanted.

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[Aardigspook]

Glad we could help. Even though my solution wasn't what you needed, it was useful for me to go through the VBA, so thanks to you too.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.