two column sort and move

[outlawspeeder]

This is fun on that Im been looking at for a long time.

I have a look up table on sheet 1, that looks like this table to the right. On sheet 2, I need it to find the table contents in the column CC, then look at a column AA and find all that match it and move all that match this cell in column AA and copy the row it to sheet 3.

Its 300 lines of data, a couple of time of week.

It is a sort of data with a twist.


<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=704 border=0><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>AA</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>BB</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>CC</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>DD</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>EE</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Tree</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Dog</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Cat</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">house</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">car</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>dave</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">man</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">boy</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">job</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">base</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Table</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>boat</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">sam</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">laura</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">sam</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">jim</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">boy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>dave</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">weed</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">walk</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">walk</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">run</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">cat</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>fun</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">laugh</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">boy</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">dive</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">float</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tree</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Dog</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">Cat</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">house</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050">dave</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">man</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">boy</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">job</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">base</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050">dave</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">weed</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">walk</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">walk</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">run</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">fun</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">laugh</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">boy</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">dive</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">float</TD></TR></TBODY></TABLE>

 

[gardnertoo]

What is the significance of the green and yellow highlights?
What is the significance of the three cells with borders?
Where did the line "boat sam laura sam jim" come from?

I am confused, can you elaborate on what you're trying to do?

 

[outlawspeeder]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1>What is the significance of the green and yellow highlights? none
What is the significance of the three cells with borders? that is a look up table
Where did the line "boat sam laura sam jim" come from? just names to fill the space

I am confused, can you elaborate on what you're trying to do?


I need code that will use info in a table look down CC column until if finds a cell that equals the table. When found, copies that row to an other tab.

To add to this, the row that copied uses what was in column AA and copy all rows that match what was found in AA to the same tab.


Look at it this way, column CC has a 1st name, move that row if it equals the table to the tab. Column AA has the last name. So found all with the same last name on the 1st tab copy them to the other tab.

I cannot post the real data.

 

[gardnertoo]

I cannot post the real data

No worries, we get that a lot.

I have created some fictional data to see if I've understood what you're trying to do. Look at the sheet sample below. The "procedure" in column I is my step-by-step summary of what I think you're after. The color coding in columns A:E highlight the rows that I think you want copied at each step in the procedure. Am I correct? If not, what have I missed?

outlawspeeder.xls
	A	B	C	D	E	F	G	H	I
1	Last name	Middle Init	First name	City	State		Lookup table		Procedure for each item in "Lookup table"
2	Taylor	-	Mary	Lindale	ME		Mary		1. Find the first "Mary". It's on row 2. Copy that row.
3	Wilson	-	Margaret	Sunnyvale	CN		William		2. That "Mary" has the last name "Taylor". Find all the Taylors. Copy them all.
4	Taylor	J	Barbara	Jacksboro	DE		Susan		3. Find the next "Mary". It's on row 8. Copy that row
5	Smith	D	Linda	Windcrest	ID		Richard		4. That "Mary" has the last name "Wilson". Find all the Wilsons. Copy them all.
6	Brown	-	Charles	Midlothian	ME				5. Find the next "Mary". It's on row 11. Copy that row
7	Wilson	M	William	Bailey's Prairie	AZ				6. That "Mary" has the last name "Brown". Find all the Browns. Copy them all.
8	Wilson	P	Mary	Del Rio	NM				7. Repeat for Mary Smith, row 17
9	Wilson	-	Dorothy	La Villa	TN				8. Find and copy all the Smiths.
10	Brown	J	William	Schertz	MI				9. Repeat for Mary Moore, row 21.
11	Brown	-	Mary	Westlake	TN				10. Find and copy all the Moores.
12	Johnson	C	James	Robinson	MS
13	Wilson	E	Dorothy	Canyon	KS
14	Moore	W	Thomas	Cibolo	NV
15	Jones	E	Maria	Glen Rose	MO
16	Smith	-	Susan	Palmview	MO
17	Smith	M	Mary	Frankston	MA
18	Moore	J	William	Colorado City	MI
19	Williams	M	Richard	Bootleg	FL
20	Smith	J	John	Forney	VA
21	Moore	-	Mary	University Park	LA
22	Jones	-	Jennifer	Brownwood	VA
Sheet1

 

[outlawspeeder]

That it! It takes me even long to explain this to the guy that sits next to me.

Can it be done or is it one of those things that is better left doing repeating filters and copy and and pasting?

 

[gardnertoo]

It can probably be done, let me play with it. I generated my fictional data set from a very small set of choices, and as a result there will be some duplicate copying done. For example, the first "William" is on row 7, he's a "Wilson", and the Wilsons have already been copied during the search for "Mary". Do you expect this to happen with the real data, and is it a problem if it does happen?

 

[outlawspeeder]

U the man. I working on being that good and the more I play the more I learn!! Thanks for all the help.

 

[gardnertoo]

Is the data you receive "a couple times a week" in a new spreadsheet each time, or updates to the existing sheet?

Is it always going to be "Sheet2" that holds the data from which you want to extract?

Will it always be "Sheet3" to which you want the extracted rows sent?

Do you require the data sent to "Sheet3" to be in the order I said in my procedure, or can it get there in any order, just as long as all the appropriate rows get copied?

Is "Sheet1" free game to add formulas, etc to?

 

[gardnertoo]

I have a solution that seems to work on my fictional data, without code. All formula based. Take the following Sheet2 sample. Highlighted in RED are the found first names from the lookuptable. No instances of Richard are found, but there are one each of William, Mary, and Susan. From these, the PINK rows are the other entries on the list who share a last name with William Martin (one), Mary Carter (three), and Susan Thompson (one).

outlawspeeder.xls
	A	B	C	D	E	F	G
1	Last name	Middle Init	First name	City	State
2	White	C	Christopher	Lindale	ME		lookuptable
3	Moore	P	David	Sunnyvale	CN		Mary
4	Martin		William	Crosby	ME		William
5	Perez	M	Cynthia	Windcrest	ID		Susan
6	Baker		Virginia	Midlothian	ME		Richard
7	Thompson	J	Anthony	Bootleg	FL
8	Allen	C	Daniel	Del Rio	NM
9	Martin	K	Margaret	Anahuac	MA
10	Turner		Timothy	Schertz	MI
11	Young	M	Amy	Westlake	TN
12	Carter		Mary	Robinson	MS
13	Rodriguez	K	Melissa	Canyon	KS
14	Allen	L	Amy	Cibolo	NV
15	Thompson	B	Susan	Glen Rose	MO
16	Jones	S	Elizabeth	Palmview	MO
17	Jones	G	Joseph	Frankston	MA
18	Walker		Catherine	Colorado City	MI
19	Carter	M	Marie	Ingleside	KS
20	Johnson		Edward	Forney	VA
21	Carter		Michael	Hardin	WA
22	Parker		Laura	Brownwood	VA
23	Harris	M	Donna	Reklaw	WI
24	Jackson	D	Michael	Bartonville	MS
25	Jackson	A	Jennifer	Sweeny	IL
26	King	M	Karen	Olton	NM
27	Carter		Rebecca	Jasper	MO
28	Garcia	P	Kenneth	Graham	IO
Sheet2

Here is Sheet1, where all the action happens. The first column holds the lookuptable. A named range, called "lookuptable", is defined by the following formula:

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

This allows you to add or remove items from the list without then having to change all the formulas. Column "C" looks through the Sheet2 data and returns the row numbers of all the entries with a match to the lookuptable entries. Column "D" puts those results in descending order. Column "E" goes through the Sheet2 data and returns the list of corresponding "last names" to create a second lookuptable. This lookup table gets it's own named range, named "lev2lookuptable", and defined by this formula:

=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-COUNTIF(Sheet1!$E:$E,0)-1,1)

Column "F" looks though Sheet2 data and returns the row numbers with entries matching the lev2lookuptable entries. Column "G" puts those in descending order.

outlawspeeder.xls
	A	B	C	D	E	F	G
1	lookuptable		Level 1	Level 1 rows	lev2lookuptable	Level 2	Level 2 rows
2	Mary		0	15	Thompson	0	27
3	William		0	12	Carter	0	21
4	Susan		4	4	Martin	4	19
5	Richard		0	0	0	0	15
6			0	0	0	0	12
7			0	0	0	7	9
8			0	0	0	0	7
9			0	0	0	9	4
10			0	0	0	0	0
11			0	0	0	0	0
12			12	0	0	12	0
13			0	0	0	0	0
14			0	0	0	0	0
15			15	0	0	15	0
16			0	0	0	0	0
17			0	0	0	0	0
18			0	0	0	0	0
19			0	0	0	19	0
Sheet1

Sheet3 does a simple OFFSET function to retrieve from Sheet2 only those rows that were selected by the formulas on Sheet1.

outlawspeeder.xls
	A	B	C	D	E
1	Last name	Middle Init	First name	City	State
2	Carter		Rebecca	Jasper	MO
3	Carter		Michael	Hardin	WA
4	Carter	M	Marie	Ingleside	KS
5	Thompson	B	Susan	Glen Rose	MO
6	Carter		Mary	Robinson	MS
7	Martin	K	Margaret	Anahuac	MA
8	Thompson	J	Anthony	Bootleg	FL
9	Martin		William	Crosby	ME
10
11
12
13
14
15
16
17
18
Sheet3

 

[gardnertoo]

I've got a VBA solution for you. The code here is adapted from a post by Tom Urtis in another thread (thanks, Tom!)

This code creates a new sheet, named "output". It mines the data from Sheet2, using the lookup table on Sheet1. It goes through Sheet2 row by row, and each time it finds a row in which the column C value is found in the lookup table, that row gets copied to "output" and highlighted bright green. Then, before it resumes it's column C search, a second loop starts at row 2 again, this time looking in column A, looking for rows whose column A value matches the column A value of the row just found by the first loop. Each of those it finds gets copied to sheet "output" and highlighted light green to differentiate it from column C find-and-copy results.

Sub data_mine()
With Application
'.ScreenUpdating = False
.DisplayAlerts = False
    
    
    Dim asn$, ListSheet$, search2$
    Dim LR&, xRow&
    Dim firstlist As Range, cell As Range, cell1 As Range, cell2 As Range, results As Range
    
    asn = ActiveSheet.Name  'sheet active at the time the macro is begun.
    ListSheet = "output"
    
    LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    xRow = 2
    
    Set firstlist = Sheets("Sheet1").Range("A2:A" & Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    Set results = Nothing
    
    On Error Resume Next
    Sheets(ListSheet).Delete
    Err.Clear
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ListSheet
     
    With Sheets(asn)
    .Rows(1).Copy Rows(1)
    
    For Each cell In .Range("C2:C" & LR).SpecialCells(2)
        For Each cell1 In firstlist
            If cell.Value = cell1.Value Then
                If Intersect(results, cell.Offset(0, -2)) Is Nothing Then    'only return non-duplicate entries
                    .Rows(cell.Row).Copy Rows(xRow)
                    Rows(xRow).Interior.ColorIndex = 4
                    xRow = xRow + 1
                    If results Is Nothing Then
                        Set results = .Cells(cell.Row, 1)
                        Else
                        Set results = Union(results, .Cells(cell.Row, 1))
                    End If
                End If
                search2 = .Cells(cell.Row, 1).Value   'sets search2 to the value in column A
                For Each cell2 In .Range("A2:A" & LR).SpecialCells(2)
                    If cell2.Value = search2 Then
                        If Intersect(results, cell2) Is Nothing Then    'only return non-duplicate entries
                            .Rows(cell2.Row).Copy Rows(xRow)
                            Rows(xRow).Interior.ColorIndex = 35
                            xRow = xRow + 1
                            Set results = Union(results, cell2)
                        End If
                    End If
                Next cell2
            End If
        Next cell1
    Next cell
    
    End With
    Columns.AutoFit

.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

In both searches, I've set it up to not copy an entry if it was already copied as the result of a previous search. In the output example below, I have manually pasted in the lookup table. The first concern is that William Martin of Crosby ME not get copied as a "Martin" after already being copied as a "William". The second concern is Mary B. Martin of Bootleg, FL. "Mary" is one of the lookup table entries, but this particular "Mary" made the list early because she too is a "Martin". When the outer loop reaches Mary B. Martin's position on Sheet2 we don't want to copy her again, and then search the list again for "Martins", because all the "Martins" have already been copied when the search found William Martin.

outlawspeeder macro version.xls
	A	B	C	D	E	F	G
1	Last name	Middle Init	First name	City	State		lookuptable
2	Martin		William	Crosby	ME		Mary
3	Martin	K	Margaret	Anahuac	MA		William
4	Martin	B	Mary	The Colony	WI		Susan
5	Thompson	J	William	Bootleg	FL		Timothy
6	Thompson	B	Susan	Glen Rose	MO
7	Turner		Timothy	Schertz	MI
8	Turner		Jerry	Best	WA
9	Carter		Mary	Robinson	MS
10	Carter	M	Marie	Ingleside	KS
11	Carter		Michael	Hardin	WA
12	Carter		Rebecca	Jasper	MO
13	Jones	G	Susan	Frankston	MA
14	Jones		Virginia	Midlothian	ME
15	Jones	S	Elizabeth	Palmview	MO
16	Jones	F	Jerry	Fulshear	MN
17	Jones		Laura	Clint	NJ
18	Taylor	K	Timothy	Goodnight	OK
19	Taylor	M	Joshua	Cuero	WA
20	Baker	D	Timothy	Austonio	CA
21	Baker		Virginia	Midlothian	ME
22	Baker	W	Thomas	Ingleside on the Bay	MD
23	Baker	D	Pamela	Fort Worth	WY
output