Copy data from one sheet to another - with merged cells

mutrus

Board Regular
Joined
Sep 10, 2002
Messages
80
Hi,

I wish to be able to read data from a range of cells on one sheet and use this data to update a range of cells on another sheet

This table shows the range to copy from:
Book1
ABCDEFGHIJKL
11234567891011
2JohnSickCourse
3MaryCourse
4Richard
5
6BlakeSick
7Ewen
8
9Trevor
Sheet1


This table shows how I want the result range to look
Book1
NOPQRSTUVWXY
121234567891011
13John00010000111
14Mary11000111111
15Richard11111111111
16
17Blake11111100000
18Ewen11111111111
19
20Trevor11111111111
Sheet1


To Explain: The source range is on a different sheet than the destination range. The first column from the source is already duplicated in the destination table however the destination range does not equal the source range (e.g John is in A2 on source and N13 on destination.

I need to be able to read each cell from the source, if the cell is empty then the corresonding cell on the destination needs to be updated to a '1'. If the source cell is a merged cell, then the cells in the destination need to updated to show a '0' for each cell that was included in the original merged cell

Hope this is not too confusing

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mutrus

Board Regular
Joined
Sep 10, 2002
Messages
80
Sorry not really, I will expand the problem a bit more. There could be up to 31 columns and up to 100 rows to iterate through. The values of each source cell that I want to pass to the target cell is the source cells color and there are up to 20 different colours which may be used. So if there is a red cell in the source then the corresponding target cell will contain a 1, if blue then 0, if yellow then 1, if red then 0 on so on.
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top