Excel - Copy contents of some cells in Sheet2 into rows with same suburb & postcode in Sheet1
The attached file contains 2 worksheets.
Sheet 1 contains 3 columns - Local Zone numbers, Postcode numbers, Suburb names. For each Postcode/Suburb combination there are many Local Zone numbers. There are also different suburb names against the same postcode number.
Sheet 2 contains 4 columns - Postcode, Suburb, Masthead name, Colour code. This list also has different suburb names against the same postcode.
The requirement is to copy the masthead name and colour code from Sheet2 into Sheet1, into the same row as the Postcode/Suburb combination, and repeat it as many times as the Postcode/Suburb combination appears.
And where there is another Masthead/Colour code for the same Postcode/Suburb, include this in the next columns.
Part of the first two masthead/colour codes have been copied manually to indicate the required result.
Masthead-to-Local-Zone-example.xlsx
Sheet 1 contains 3 columns - Local Zone numbers, Postcode numbers, Suburb names. For each Postcode/Suburb combination there are many Local Zone numbers. There are also different suburb names against the same postcode number.
Sheet 2 contains 4 columns - Postcode, Suburb, Masthead name, Colour code. This list also has different suburb names against the same postcode.
The requirement is to copy the masthead name and colour code from Sheet2 into Sheet1, into the same row as the Postcode/Suburb combination, and repeat it as many times as the Postcode/Suburb combination appears.
And where there is another Masthead/Colour code for the same Postcode/Suburb, include this in the next columns.
Part of the first two masthead/colour codes have been copied manually to indicate the required result.
Masthead-to-Local-Zone-example.xlsx
als315
Can it be done in vba?
It can be done by a few filtering, copying, and remove duplicates.
Something is wrong with the data.
Only 2 Masthead types, Central Courier, and Wentworth Courier, where result has 9.
There are no Masthead values below row 191 in the data.
Most of the Postcode-Suburbs-Masthead on the result sheet are not on the input sheet.
Some Suburbs are not there at all.
Examples row 5, Town Hall, row 14, East Sydney
Please upload a new sheet with all data.
Something is wrong with the data.
Only 2 Masthead types, Central Courier, and Wentworth Courier, where result has 9.
There are no Masthead values below row 191 in the data.
Most of the Postcode-Suburbs-Masthead on the result sheet are not on the input sheet.
Some Suburbs are not there at all.
Examples row 5, Town Hall, row 14, East Sydney
Please upload a new sheet with all data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, misunderstood the requirement.
Skip my comment.
Skip my comment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is very good to have many experts.
My code - 374 s
Egil's - 55 s
Subodh is a champion with 23 s (my computer seems not to be so fast)
I hope it is one time task, so did not spend many time to optimization
My code - 374 s
Egil's - 55 s
Subodh is a champion with 23 s (my computer seems not to be so fast)
I hope it is one time task, so did not spend many time to optimization
ASKER
Thank you all for you help on this.
I tried them all.
als315 - your version appeared to omit the first and last in any group
Subodh - your version also appeared to omit the first and last in any group.
Ejgil - your version took the longest but it appears to copy against all matching postcodes/suburbs.
I tried them all.
als315 - your version appeared to omit the first and last in any group
Subodh - your version also appeared to omit the first and last in any group.
Ejgil - your version took the longest but it appears to copy against all matching postcodes/suburbs.
ASKER
Thanks very much for your help.