Please can someone let me know the best way to approach this challenge - I need to create a Master sheet capturing data week by week during the year from another sheet each week. The issue is lining the data when the client's locations will not always be the same (income is against the location).
I have tried to show what I need to achieve below:
[TABLE="width: 420"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]23/09/2018
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]99.45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Harding House
[/TD]
[TD="bgcolor: transparent, align: right"]174.38
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]338.95
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]81.83
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]77.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]46 Juliana Close
[/TD]
[TD="bgcolor: transparent, align: right"]55.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]150.82
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 420"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]30/09/2018
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]85.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brookside Drive
[/TD]
[TD="bgcolor: transparent, align: right"]72.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]55.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Diamond Resourcing
[/TD]
[TD="bgcolor: transparent"]Leadenhall Market
[/TD]
[TD="bgcolor: transparent, align: right"]77.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]62.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]66 Titian Street
[/TD]
[TD="bgcolor: transparent, align: right"]35.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]88.00
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 516"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]Master Spreadsheet
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit 23/9
[/TD]
[TD="bgcolor: transparent, colspan: 2"]Proft 30/9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]99.45
[/TD]
[TD="bgcolor: transparent, align: right"]85.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Harding House
[/TD]
[TD="bgcolor: transparent, align: right"]174.38
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brookside Drive
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]72.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]338.95
[/TD]
[TD="bgcolor: transparent, align: right"]250.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]81.83
[/TD]
[TD="bgcolor: transparent, align: right"]55.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Diamond Resourcing
[/TD]
[TD="bgcolor: transparent"]Leadenhall Market
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]77.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]77.29
[/TD]
[TD="bgcolor: transparent, align: right"]62.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]46 Juliana Close
[/TD]
[TD="bgcolor: transparent, align: right"]55.29
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]66 Titian Street
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]35.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]150.82
[/TD]
[TD="bgcolor: transparent, align: right"]88.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I have tried to show what I need to achieve below:
[TABLE="width: 420"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]23/09/2018
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]99.45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Harding House
[/TD]
[TD="bgcolor: transparent, align: right"]174.38
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]338.95
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]81.83
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]77.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]46 Juliana Close
[/TD]
[TD="bgcolor: transparent, align: right"]55.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]150.82
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 420"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]30/09/2018
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]85.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brookside Drive
[/TD]
[TD="bgcolor: transparent, align: right"]72.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]55.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Diamond Resourcing
[/TD]
[TD="bgcolor: transparent"]Leadenhall Market
[/TD]
[TD="bgcolor: transparent, align: right"]77.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]62.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]66 Titian Street
[/TD]
[TD="bgcolor: transparent, align: right"]35.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]88.00
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 516"]
<tbody>[TR]
[TD="width: 285, bgcolor: transparent"]Master Spreadsheet
[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Company
[/TD]
[TD="bgcolor: transparent"]Location
[/TD]
[TD="bgcolor: transparent"]Profit 23/9
[/TD]
[TD="bgcolor: transparent, colspan: 2"]Proft 30/9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brondesbury Road
[/TD]
[TD="bgcolor: transparent, align: right"]99.45
[/TD]
[TD="bgcolor: transparent, align: right"]85.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Harding House
[/TD]
[TD="bgcolor: transparent, align: right"]174.38
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Action on Hearing Loss (previously RNID)
[/TD]
[TD="bgcolor: transparent"]Brookside Drive
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]72.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clarion Housing (formerly Circle Anglia/Centra)
[/TD]
[TD="bgcolor: transparent"]Brentwood Foyer
[/TD]
[TD="bgcolor: transparent, align: right"]338.95
[/TD]
[TD="bgcolor: transparent, align: right"]250.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Community Options
[/TD]
[TD="bgcolor: transparent"]Bromley Flexible Support Service
[/TD]
[TD="bgcolor: transparent, align: right"]81.83
[/TD]
[TD="bgcolor: transparent, align: right"]55.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Diamond Resourcing
[/TD]
[TD="bgcolor: transparent"]Leadenhall Market
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]77.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]27 Sampson Avenue
[/TD]
[TD="bgcolor: transparent, align: right"]77.29
[/TD]
[TD="bgcolor: transparent, align: right"]62.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]46 Juliana Close
[/TD]
[TD="bgcolor: transparent, align: right"]55.29
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]66 Titian Street
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]35.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dimensions
[/TD]
[TD="bgcolor: transparent"]Matlock Close 4
[/TD]
[TD="bgcolor: transparent, align: right"]150.82
[/TD]
[TD="bgcolor: transparent, align: right"]88.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]