Hi!
I am working with about 400k rows of data, each with an individual identity. Tracking jobs.
I have divided the 400k rows into separate tabs based on project IDs.
Every week, some jobs will be completed, and I will need to display what has been completed and what remains outstanding.
So I will receive new data of only what remains outstanding.
How do integrate the new data to show what has been completed, and what is remaining?
Thanks.
Hi and welcome to our forum!
First of all, you should put all the data back into one tab as this is the way Excel was intended to be used and will make your life much easier because the tools like PivotTables and functions are designed to work with data in a tabular format. You can always split it out into separate tabs later with PivotTables as explained here.
You can use Power Query to compare the data in different tables (this week vs last week etc.) and then extract just those records that are still active. You'll need to decide how you identify such records, as I can't tell from what you've described. Presumably there is a date or other field that marks a project as completed.
I hope that points you in the right direction.
Mynda
Hi Mynda
Thank you for your prompt response. Now, I have a problem, the power query editor does not give me the option to close and load to, only to close and load. Here is a screenshot. i am not sure how to work around this.
I have office 365
Allow it to Close & Load, then in the Queries pane on the right of the Excel Workbook, right-click the query > Load to. Here you can change the load settings.
Kind regards,
Mynda
