Importing data week by week

M

marcidee

Board Regular
Joined
May 23, 2016
Messages
196
Office Version
  1. 2019
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]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
The best way to do this is to use vlookup on your master. What works well is having all of your clients in column A and then using vlookup in the data columns you need. The next question is, are all your weeks on one sheet or broken down with each week having its own sheet?
 
Upvote 0
Thank you for your prompt reply - each week a new sheet will be generated with the data needing to be merged.

I have tried Vlookup with Indx/Match - the problem is I can't have all the clients on the master from the start as there will be new locations regularly added - and that's where I think there is a problem
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top