If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 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. |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|