Copy data from one sheet to another - with merged cells

mutrus

Board Regular
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DonkeyOte

MrExcel MVP
shouldn't be too difficult...

O13 = if(sheet1!b2<>"",1,0)
etc...

mutrus

Board Regular
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.

Replies
7
Views
271
Replies
1
Views
227
Replies
7
Views
241
Replies
8
Views
335
Replies
1
Views
242

1,148,159
Messages
5,745,111
Members
423,924
Latest member
Gazzat

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.

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

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