Excel - Copy contents of cells in Sheet1 into rows with same postcode in Sheet2. and repeat as many times as the postcode occurs

Sheet1 contains 9 columns - the key ID is the Postcode. Each postcode isbe repeated several times with different  contents in the cells on each row for that postcode number.

Sheet2 contains 2 columns - Postcode, Local Zone. Each postcode is repeated several times with different numbers in the Local Zone column.

The requirement is to copy the row by postcode from Sheet1, into the row with the same postcode in Sheet2, and to repeat the copy as many times as there are different Local Zone numbers, and the create a new set of the same postcode/local zones for the next postcode from Sheet1.  Each row in Sheet1 is created in Sheet2 for as many times as the LocalZone number occurs.

The example file shows the first postcode contents from Sheet1, copied manually into Sheet2.
Example-masthead-to-local-zone-2017.xlsx
gregfthompsonAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Try attached.
The list of local zones (unique) are on sheet "Base Local".
The list is sorted on Postcode and Local Zone by the program.
The result sheet Local are cleared before new values are copied from "Base Local" and Masthead sheets.

Some postcodes are not in the local list, so nothing is copied from Masthead sheet to Local sheet.
The postcodes are marked yellow.
There are also postcodes in the local list, not on Masthead sheet.
Example-masthead-to-local-zone-2017.xlsm
0
 
gregfthompsonAuthor Commented:
Thanks Heaps. Brilliant piece of work.

I want to include another column on the masthead page, and I changed this part of your code - I1 to J1:
wsMasthead.Range("B1:J1").Copy wsLocal.Range("C1")

When the script ran, it included the column heading for J, but did not include the contents for that column.

Can you make the correct adjustment?

Thanks,
Greg
0
 
Ejgil HedegaardCommented:
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
gregfthompsonAuthor Commented:
Oops.

I found the other line to adjust from I to J.
It is now running again.


Thanks again.

Greg
0
 
gregfthompsonAuthor Commented:
Thanks.
Top job.
0
 
gregfthompsonAuthor Commented:
PS. I want to include some more columns in the Base Local sheet.
Do I set up a new question?
Thanks,
Greg
0
 
Ejgil HedegaardCommented:
If all the columns from Base Local go first on the Local sheet, and Masthead columns right to that, it is just a minor change.
Upload a workbook with the setup, and I will fix it.
0
 
gregfthompsonAuthor Commented:
Thanks.
Original example attached with additional worksheet Base Local - this worksheet includes the additional columns.
Example-masthead-to-local-zone---w-.xlsx
0
 
Ejgil HedegaardCommented:
I have changed the code to use the columns on the sheets, so you can add more columns.
Sorting on Masthead sheet on Postcode, to ensure that Masthead and Base Local are in the same order.
You have moved Postcode on Base Local to column B, code adjusted for that.
The column widths from Base Local and Masthead copied to Local.
Progress indicator, status at the lower left of the sheet.
Added a few comments in the code.
Example-masthead-to-local-zone-2017.xlsm
0
 
gregfthompsonAuthor Commented:
Absolutely brilliant.
Deserves more points.

Thank you very much.

Greg
0
 
gregfthompsonAuthor Commented:
Hello Ejgil,

I have another worksheet called RadioTV to replace the Masthead worksheet. It has different headings.
I tried simply copying it into Masthead worksheet, and leaving the Masthead, but when the script ran, it kept going for about 80 minutes before I decided that perhaps something is not working correctly.

As the script probably requires only a small revision, I thought I would ask you when would be suitable for me to upload a new question with the RadioTV worksheet?

Thanks,
Greg
0
 
gregfthompsonAuthor Commented:
PS That way you can answer it and get the points.
0
 
Ejgil HedegaardCommented:
Do that, and make a reference here, so it is easy to find, and I will look at it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.