How to select certain Rows?

[Mark53]

I think this is easy, but I can't figure it out.
Maybe use "trainer" rows (is that what they're called?)...

In Excel 2007, I've got two worksheets.
Column ONE is the only important one.

WS1 looks like...
1 other misc data
2 other misc data
3 other misc data
4 other misc data
5 other misc data
6 other misc data

and WS2 looks like...
2 some stuff
3 some stuff
5 some stuff

How do I eliminate the rows in WS1
that don't have matching info in WS2 (like 1 & 4 & 6) ?

Thanks.

Mark53

[MarvinP]

Hi Mark53,

I'd create a new column in the WS1 and in this column have a VLookup formula that would return "Keep" if it found matching stuff in WS2. Otherwise it would return a "Delete". I'd copy this formula down for all the rows in WS1, sort the rows by this column's value and delete the big block of "Delete".

Hope this makes sense. If not attach a sample file.

[Mark53]

Hi Mark53,
I'd create a new column in the WS1 and in this column have a VLookup formula
...
I'd copy this formula down for all the rows in WS1

Yeah... I now remember... it's called a Helper column.
But... how would I copy it all the way down? I do Not want to do that manually... there are over 600,000 rows !

Thanks.

[MarvinP]

Hey Mark - you're going to like this answer.

Excel has thought of this and many people don't know how to do this.
Do the first formula in the top row. Then click on this formula and look at the cell. The bottom right corner of the cell will have a little dot or handle on it. Using your mouse, double click on this handle and it will fill in all the rows down to row 600,000. see http://www.jegsworks.com/Lessons/num...s/autofill.htm

[Mark53]

MARVIN ! THAT is Bloody Brilliant! Thanks very much. I LOVE it !
Mark53