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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.