Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find a Name, Duplicate a Row, Change a Value
Here's the problem:
Sheet is 20 columns by 1,000 rows. Column B contains 1,000 names. I want to search for a name. When found, I want to duplicate the row in which the name was found. Then I want to change one value in that row (in column D). I want to repeat this operation for many names. Any ideas? |
#2
|
|||
|
|||
One way ..
Assume you have a table In Sheet1 ------------- SN Name Field1 Field2 Field3 etc 1 Name1 Data1 Data1 Data1 2 Name2 Data2 Data2 Data2 3 Name3 Data3 Data3 Data3 4 Name4 Data4 Data4 Data4 5 Name5 Data5 Data5 Data5 etc (Names in col B assumed unique) In Sheet2 ------------- Suppose you've listed the names in A2 down: Name4 Name5 etc Put in B2: =IF(ISNA(MATCH($A2,Sheet1!$B:$B,0)),"",OFFSET(Shee t1!$A$1,MATCH($A2,Sheet1!$ B:$B,0)-1,COLUMN(A2)-1)) Copy B2 across as many cols as there are in the table in Sheet1, then fill down The above will extract the rows of data from Sheet1 corresponding to the names listed in col A Any unmatched names will return blanks: "" You could then do a copy paste special values of the extracted results into another sheet and change the values in column D as desired -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bob" wrote in message ... Here's the problem: Sheet is 20 columns by 1,000 rows. Column B contains 1,000 names. I want to search for a name. When found, I want to duplicate the row in which the name was found. Then I want to change one value in that row (in column D). I want to repeat this operation for many names. Any ideas? |
#3
|
|||
|
|||
I left out one important point--i want to perform all of the steps I cited
automatically without any manual intervention. I also do not want to paste values, I want a regular paste operation. "Max" wrote: One way .. Assume you have a table In Sheet1 ------------- SN Name Field1 Field2 Field3 etc 1 Name1 Data1 Data1 Data1 2 Name2 Data2 Data2 Data2 3 Name3 Data3 Data3 Data3 4 Name4 Data4 Data4 Data4 5 Name5 Data5 Data5 Data5 etc (Names in col B assumed unique) In Sheet2 ------------- Suppose you've listed the names in A2 down: Name4 Name5 etc Put in B2: =IF(ISNA(MATCH($A2,Sheet1!$B:$B,0)),"",OFFSET(Shee t1!$A$1,MATCH($A2,Sheet1!$ B:$B,0)-1,COLUMN(A2)-1)) Copy B2 across as many cols as there are in the table in Sheet1, then fill down The above will extract the rows of data from Sheet1 corresponding to the names listed in col A Any unmatched names will return blanks: "" You could then do a copy paste special values of the extracted results into another sheet and change the values in column D as desired -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bob" wrote in message ... Here's the problem: Sheet is 20 columns by 1,000 rows. Column B contains 1,000 names. I want to search for a name. When found, I want to duplicate the row in which the name was found. Then I want to change one value in that row (in column D). I want to repeat this operation for many names. Any ideas? |
#4
|
|||
|
|||
Then .. perhaps you could elaborate on your intent in ..
Then I want to change one value in that row (in column D). Do an arithmetic calc on the extracted value in col D ? What is this change you want to make in col D? What exactly is in col D? etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bob" wrote in message ... I left out one important point--i want to perform all of the steps I cited automatically without any manual intervention. I also do not want to paste values, I want a regular paste operation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
find duplicate cells in Excel | Excel Discussion (Misc queries) | |||
How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) | |||
how to change "Find" feature | Excel Worksheet Functions |