Compare Worksheets and Copy/Paste Data

I have 2 worksheets... the first one is the "master sheet" while the second
one is a "working sheet".

Both worksheets contain the same data types (text) in column A and B.

More Information:
- Currently, the mastersheet contains a static set of let's say 500 "Task
No" (1st level, 2nd level, 3rd level... e.g. "1", "1.1", "1.1.1").
- The working sheet also contains the Task No but it may have only a small
subset of them (let's say only 15 task numbers out of 500 task numbers)
- The mastersheet does not have any Task Names while the working sheet has
the 15 Task Names that correspond to the 15 task numbers.


Here's what I need to achieve:
1. Wherever there is a match of the Task No in both worksheets (column A),
copy the Task Name from the working sheet in Column B and paste it into the
appropriate cell of column B of the Master Sheet.
2. Please see the BEFORE & AFTER samples below (which shows the the row
number will not be the same for the two working sheets.


Sample BEFORE Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do x
1.1 1.3.2 do y
1.2 2.2 do z
1.3
1.3.1
1.3.2
1.4
2
2.1
2.2
2.2.1
2.2.2



Sample AFTER Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do task x
1.1 1.3.2 do task y
1.2 do task x 2.2 do task z
1.3
1.3.1
1.3.2 do task y
1.4
2
2.1
2.2 do task z
2.2.1
2.2.2



Now my Question:
Is there a way to write a function or macro that reads column A in both
worksheets and where it finds a match in the Task No (text format since I'll
have e.g. numbers such as "1.1.1"), then copies the Task Names values from
the working sheet and paste those values into the appropriate Task Name cell
(column B) of the Mastersheet?


Thanks,
Tom

Hi tom
though this is trcitly spoken not a 'copy and paste' procedure try the
following formula in cell B1 on your mastersheet
=IF(ISNA(VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0)),"",VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0))

and copy this down


--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
I have 2 worksheets... the first one is the "master sheet" while the
second one is a "working sheet".

Both worksheets contain the same data types (text) in column A and B.

More Information:
- Currently, the mastersheet contains a static set of let's say 500
"Task No" (1st level, 2nd level, 3rd level... e.g. "1", "1.1",
"1.1.1"). - The working sheet also contains the Task No but it may
have only a small subset of them (let's say only 15 task numbers out
of 500 task numbers) - The mastersheet does not have any Task Names
while the working sheet has the 15 Task Names that correspond to the
15 task numbers.


Here's what I need to achieve:
1. Wherever there is a match of the Task No in both worksheets
(column A), copy the Task Name from the working sheet in Column B and
paste it into the appropriate cell of column B of the Master Sheet.
2. Please see the BEFORE & AFTER samples below (which shows the the
row number will not be the same for the two working sheets.


Sample BEFORE Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do x
1.1 1.3.2 do y
1.2 2.2 do z
1.3
1.3.1
1.3.2
1.4
2
2.1
2.2
2.2.1
2.2.2



Sample AFTER Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do
task x
1.1 1.3.2 do
task y
1.2 do task x 2.2 do task
z
1.3
1.3.1
1.3.2 do task y
1.4
2
2.1
2.2 do task z
2.2.1
2.2.2



Now my Question:
Is there a way to write a function or macro that reads column A in
both worksheets and where it finds a match in the Task No (text
format since I'll have e.g. numbers such as "1.1.1"), then copies the
Task Names values from the working sheet and paste those values into
the appropriate Task Name cell (column B) of the Mastersheet?


Thanks,
Tom

Frank:

Thanks for your prompt response. This absolutely works great!!!

I now realize though that I forgot to mention 1 potential scenario that
might calls for a challenge.

There could be instances that team members repeat the same Task No more than
1 time in the working sheet. Essentially, this means that specific
"subtasks" relate to a task in the master sheet.

So, it should look like this AFTER THE VLOOKUP (I now have chosen a very
small sample datasubset):

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col B
1 1.2 do x
1.1 1.2 do y
1.2 do x; do y, do z 1.2 do z
1.3 do a 1.3 do a

Or even better (it might need numeric identifiers)....

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col B
1 1.2 do
x
1.1 1.2 do
y
1.2 1) do x, 2) do y, 3) do z 1.2 do z
1.3 do a 1.3 do a


Is either solution (with or without numbering) -- but with concatenating --
possilbe?


Thanks in advance,
Tom





"Frank Kabel" wrote in message
...
Hi tom
though this is trcitly spoken not a 'copy and paste' procedure try the
following formula in cell B1 on your mastersheet
=IF(ISNA(VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0)),"",VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0))

and copy this down


--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
I have 2 worksheets... the first one is the "master sheet" while the
second one is a "working sheet".

Both worksheets contain the same data types (text) in column A and B.

More Information:
- Currently, the mastersheet contains a static set of let's say 500
"Task No" (1st level, 2nd level, 3rd level... e.g. "1", "1.1",
"1.1.1"). - The working sheet also contains the Task No but it may
have only a small subset of them (let's say only 15 task numbers out
of 500 task numbers) - The mastersheet does not have any Task Names
while the working sheet has the 15 Task Names that correspond to the
15 task numbers.


Here's what I need to achieve:
1. Wherever there is a match of the Task No in both worksheets
(column A), copy the Task Name from the working sheet in Column B and
paste it into the appropriate cell of column B of the Master Sheet.
2. Please see the BEFORE & AFTER samples below (which shows the the
row number will not be the same for the two working sheets.


Sample BEFORE Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do x
1.1 1.3.2 do y
1.2 2.2 do z
1.3
1.3.1
1.3.2
1.4
2
2.1
2.2
2.2.1
2.2.2



Sample AFTER Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do
task x
1.1 1.3.2 do
task y
1.2 do task x 2.2 do task
z
1.3
1.3.1
1.3.2 do task y
1.4
2
2.1
2.2 do task z
2.2.1
2.2.2



Now my Question:
Is there a way to write a function or macro that reads column A in
both worksheets and where it finds a match in the Task No (text
format since I'll have e.g. numbers such as "1.1.1"), then copies the
Task Names values from the working sheet and paste those values into
the appropriate Task Name cell (column B) of the Mastersheet?


Thanks,
Tom

Hi Tom
using only Excel formulas: not possible - this requires macros.
You might consider using alan Beban's Arrays functions. It includes a
VLOOKUPS function which returns multiple lookup matches (haven't tested
it by myself though)
You'll find these functions at: http://home.pacbell.net/beban

--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
Frank:

Thanks for your prompt response. This absolutely works great!!!

I now realize though that I forgot to mention 1 potential scenario
that might calls for a challenge.

There could be instances that team members repeat the same Task No
more than 1 time in the working sheet. Essentially, this means that
specific "subtasks" relate to a task in the master sheet.

So, it should look like this AFTER THE VLOOKUP (I now have chosen a
very small sample datasubset):

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col B
1 1.2 do x
1.1 1.2 do y
1.2 do x; do y, do z 1.2 do z
1.3 do a 1.3 do a

Or even better (it might need numeric identifiers)....

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col
B
1 1.2
do x
1.1 1.2
do y
1.2 1) do x, 2) do y, 3) do z 1.2 do z
1.3 do a 1.3
do a


Is either solution (with or without numbering) -- but with
concatenating -- possilbe?


Thanks in advance,
Tom





"Frank Kabel" wrote in message
...
Hi tom
though this is trcitly spoken not a 'copy and paste' procedure try
the following formula in cell B1 on your mastersheet
=IF(ISNA(VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0)),"",VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0))

and copy this down


--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
I have 2 worksheets... the first one is the "master sheet" while
the
second one is a "working sheet".

Both worksheets contain the same data types (text) in column A and
B.

More Information:
- Currently, the mastersheet contains a static set of let's say 500
"Task No" (1st level, 2nd level, 3rd level... e.g. "1", "1.1",
"1.1.1"). - The working sheet also contains the Task No but it may
have only a small subset of them (let's say only 15 task numbers
out
of 500 task numbers) - The mastersheet does not have any Task Names
while the working sheet has the 15 Task Names that correspond to
the
15 task numbers.


Here's what I need to achieve:
1. Wherever there is a match of the Task No in both worksheets
(column A), copy the Task Name from the working sheet in Column B
and paste it into the appropriate cell of column B of the Master
Sheet.
2. Please see the BEFORE & AFTER samples below (which shows the the
row number will not be the same for the two working sheets.


Sample BEFORE Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do x
1.1 1.3.2 do y
1.2 2.2 do z
1.3
1.3.1
1.3.2
1.4
2
2.1
2.2
2.2.1
2.2.2



Sample AFTER Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2
do task x
1.1 1.3.2 do
task y
1.2 do task x 2.2 do
task
z
1.3
1.3.1
1.3.2 do task y
1.4
2
2.1
2.2 do task z
2.2.1
2.2.2



Now my Question:
Is there a way to write a function or macro that reads column A in
both worksheets and where it finds a match in the Task No (text
format since I'll have e.g. numbers such as "1.1.1"), then copies
the Task Names values from the working sheet and paste those values
into the appropriate Task Name cell (column B) of the Mastersheet?


Thanks,
Tom

Frank:

Thanks for the feedback... I have downloaded the Array XLS... I'll have a
look if one of the functions meets my need.

--
Thanks again,
Tom


"Frank Kabel" wrote in message
...
Hi Tom
using only Excel formulas: not possible - this requires macros.
You might consider using alan Beban's Arrays functions. It includes a
VLOOKUPS function which returns multiple lookup matches (haven't tested
it by myself though)
You'll find these functions at: http://home.pacbell.net/beban

--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
Frank:

Thanks for your prompt response. This absolutely works great!!!

I now realize though that I forgot to mention 1 potential scenario
that might calls for a challenge.

There could be instances that team members repeat the same Task No
more than 1 time in the working sheet. Essentially, this means that
specific "subtasks" relate to a task in the master sheet.

So, it should look like this AFTER THE VLOOKUP (I now have chosen a
very small sample datasubset):

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col B
1 1.2 do x
1.1 1.2 do y
1.2 do x; do y, do z 1.2 do z
1.3 do a 1.3 do a

Or even better (it might need numeric identifiers)....

Mastersheet Working Sheet
===================== =====================
Col A Col B Col A Col
B
1 1.2
do x
1.1 1.2
do y
1.2 1) do x, 2) do y, 3) do z 1.2 do z
1.3 do a 1.3
do a


Is either solution (with or without numbering) -- but with
concatenating -- possilbe?


Thanks in advance,
Tom





"Frank Kabel" wrote in message
...
Hi tom
though this is trcitly spoken not a 'copy and paste' procedure try
the following formula in cell B1 on your mastersheet
=IF(ISNA(VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0)),"",VLOOKUP(A1,'working
sheet'!$A$1:$B$100,2,0))

and copy this down


--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
I have 2 worksheets... the first one is the "master sheet" while
the
second one is a "working sheet".

Both worksheets contain the same data types (text) in column A and
B.

More Information:
- Currently, the mastersheet contains a static set of let's say 500
"Task No" (1st level, 2nd level, 3rd level... e.g. "1", "1.1",
"1.1.1"). - The working sheet also contains the Task No but it may
have only a small subset of them (let's say only 15 task numbers
out
of 500 task numbers) - The mastersheet does not have any Task Names
while the working sheet has the 15 Task Names that correspond to
the
15 task numbers.


Here's what I need to achieve:
1. Wherever there is a match of the Task No in both worksheets
(column A), copy the Task Name from the working sheet in Column B
and paste it into the appropriate cell of column B of the Master
Sheet.
2. Please see the BEFORE & AFTER samples below (which shows the the
row number will not be the same for the two working sheets.


Sample BEFORE Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2 do x
1.1 1.3.2 do y
1.2 2.2 do z
1.3
1.3.1
1.3.2
1.4
2
2.1
2.2
2.2.1
2.2.2



Sample AFTER Function/Macro is Executed:

Mastersheet Working Sheet
===================== =====================

Col A Col B Col A Col B
1 1.2
do task x
1.1 1.3.2 do
task y
1.2 do task x 2.2 do
task
z
1.3
1.3.1
1.3.2 do task y
1.4
2
2.1
2.2 do task z
2.2.1
2.2.2



Now my Question:
Is there a way to write a function or macro that reads column A in
both worksheets and where it finds a match in the Task No (text
format since I'll have e.g. numbers such as "1.1.1"), then copies
the Task Names values from the working sheet and paste those values
into the appropriate Task Name cell (column B) of the Mastersheet?


Thanks,
Tom