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

[dave chamberlan]

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/tutorial...d9-accc915af876/using-comcallable-wrappe.aspx

 

[zvkmpw]

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
A2100.

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.

 

[Fred Smith]

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

Regards,
Fred.

in message
news:[email protected]...