Compare Worksheets and Copy/Paste Data

  • Thread starter Thread starter Tom Bock
  • Start date Start date
T

Tom Bock

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
 
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
 
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
 
Frank:

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