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?

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?

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?

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.