copy entire row if phrase is present

[fearfour]

Hi there, I am hoping that someone can help me out, I would really appreciate it! (Please note that I searched through the forum for an answer and found something sort of similar but not close enough, at least not for me to alter it at my skill level!)

Here's my situation and what I need to do:

In several worksheets (all within the same workbook), I have a red flag column in which either the phrase "Price too High" or "Price too low" appears. If the price is not too outlandish, then nothing appears in this column. In each of the worksheets, the red flag column is always in column R.

I am trying to look through each of the worksheets and if either of these phrases is present, I would like to copy the Entire Row into a worksheet called "Bid Analysis Summary".

The worksheets in which I would like to look contain varying numbers of rows of data, some are hundreds of rows long, some only have a few rows.

Although I am quite skilled with IF forlumas and such, my knowledge of VLOOKUP and the like is limited.

I can do this either with a macro or a formula....I just want to make it happen at this point as I am hitting the wall on this project!

Thank you so much for your help, you guys are great

 

[fearfour]

Anyone? I would really appreciate any help that you could offer.....

 

[dpc1l]

I'm not sure what the best way to solve this problem is, but I think I might have a way (from this board) that could help. I can't remember who it was that posted it originally - sorry

Copy all the contents of the sheets into the "bid analysis summary" (either manually, or record a macro if there are too many to do regularly)
How does the flag appear in column R? I'm assuming it's a value but the font is different? If so, this should work, if not.... I'm not skilled enough to help.

you'll need two empty columns in "bid analysis summary"
In the first one, use an IF statement to create a value if the flag is present in column R (eg, =IF(R1="(whatever represents a flag)",1,0)).
This step is only necessray if the flag value is text rather than digits.
In the second column, create a formula dividing 0 by this new value. If there are no flags in R, it should return #DIV/0
Highlight the entire column and go to Edit - go to - special - formulas, and untick all boxes except "errors". Now go to edit, delete, and choose entire rows. You should now be left with the rows with flags in.

This is a work around, not a solution, really, and I'm sure there will be less cumbersome solutions.

 

[fearfour]

Thanks dpc, but I want to make this automated though so that if somethign changes within the sheets it will automatically appear or disappear from the summary sheet.

Anyone else know of a more automated solution?

Thanks in advance!

 

[fearfour]

Also.....

Just to clarify, the "flag" is indeed the phrase "Price too high" and "Price too low" .... whichever the case might be.....

Thanks for replying dpc1l !

 

[fearfour]

Please, if there is anyone out there who can offer help I am running out of time on this project!

Thank you so much!

 

[chicagodiceman]

Welcome to the board:

The worksheets in which I would like to look contain varying numbers of rows of data, some are hundreds of rows long, some only have a few rows.

Is there anything in rows beyond the last row that contains data?

 

[fearfour]

Yes there is

but it is just extraneous things like the word "Total" and such.....I can remove it if necessary....

 

[pgc01]

Hi fearfour

I posted today a solution to a problem like yours. Please read the whole thread carefully and you may be able to adapt the solution to your case.

http://www.mrexcel.com/board2/viewtopic.php?t=233494

If you find it a good start-up point for your solution and have doubts, I may be able to answer you tomorrow.

Hope this helps
PGC

 

[fearfour]

Hi PCG, thank you for replying!

Unfortunately though, I am not getting this to work. My problems are that 1) I need to copy the ENTIRE ROW, and 2) I need to copy the row if either of two conditions is met, not just one (they being if within column R there appears the phrase "Price too high" OR "Price too low".


I tried editing your macro to make it work but I am not having luck.

If you have more input I would welcome your help, it looks like this will be a very long night!

Thanks again!