Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy entire row to another sheet based on a criteria | Excel Discussion (Misc queries) | |||
Moving An Entire Row From One Sheet To Another | Excel Discussion (Misc queries) | |||
Finding the value of a cell in an entire worksheet | Excel Worksheet Functions | |||
Moving rows from one sheet to another based on a particular cell v | Excel Discussion (Misc queries) | |||
Moving cursor to another cell w/out selecting entire area | Excel Discussion (Misc queries) |