finding and then moving entire row to new sheet based on cell value
 

I have a sheet with several thousand rows that looks something like this:

name address city search-term

I want to be able to "search" the search-term column for a key word and then move entire row to a new sheet

There are several examples here I have tried none seem to do above!

Thanks for any guidance.

Dave

EggHeadCafe - Software Developer Portal of Choice
Using COM-Callable Wrappers to Extend Visual Basic 6.0
http://www.eggheadcafe.com/tutorials...le-wrappe.aspx

finding and then moving entire row to new sheet based on cellvalue
 

I have a sheet with several thousand rows that looks something like this:

name address city search-term

I want to be able to "search" the search-term column for a key word and then move entire row to a new sheet

In my example, Sheet1 has the data for the four specified columns in
A2:D100.

Reserve Sheet2!A1 for the search-term to be matched.

In Sheet2!B2 put
=IF(Sheet1!D2=$A$1,1+MAX(B$1:B1),"")

In Sheet2!C2 put
=IF(ROW()MAX($B:$B)+1,"",OFFSET(Sheet1!$A$1,MATCH (ROW()-1,$B:$B,
0)-1,COLUMN()-3))

Select Sheet2!C2 and copy to F2.

Select Sheet2!B2:F2 and copy down to row 100.

The desired rows should be in Sheet2!C:F.

You might find empty cells in Sheet1 turning out to be zeros in
Sheet2. To avoid this, replace the OFFSET() part of the formula with:
IF(OFFSET(...)="","",OFFSET(...))
putting the same arguments as above in both OFFSET()s.

Modify to suit.

finding and then moving entire row to new sheet based on cell value
 

Have you tried Autofilter? Choose your search from the search-term column,
then copy the visible rows.

Regards,
Fred.

"dave chamberlan" wrote in message
...
I have a sheet with several thousand rows that looks something like this:

name address city search-term

I want to be able to "search" the search-term column for a key word and
then move entire row to a new sheet

There are several examples here I have tried none seem to do above!

Thanks for any guidance.

Dave

EggHeadCafe - Software Developer Portal of Choice
Using COM-Callable Wrappers to Extend Visual Basic 6.0
http://www.eggheadcafe.com/tutorials...le-wrappe.aspx