copy entire row if phrase is present

fearfour

New Member
Joined
Sep 19, 2006
Messages
14
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dpc1l

New Member
Joined
May 25, 2006
Messages
7
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

New Member
Joined
Sep 19, 2006
Messages
14
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

New Member
Joined
Sep 19, 2006
Messages
14

ADVERTISEMENT

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

New Member
Joined
Sep 19, 2006
Messages
14
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

Active Member
Joined
Mar 13, 2004
Messages
371

ADVERTISEMENT

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

New Member
Joined
Sep 19, 2006
Messages
14
Yes there is

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

New Member
Joined
Sep 19, 2006
Messages
14
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!
 

Forum statistics

Threads
1,136,366
Messages
5,675,351
Members
419,563
Latest member
blairtab

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top