I run reports monthly in 3 different help desk ticketing systems that we use and then export results to worksheets in my Excel 2007 "master file". In these worksheets, I have the same type of data in the same columns, with the same column headers on all 3 worksheets. Many of these tickets get updated monthly, and so I have to capture those updated records into the master file. Is there an easier way to deal with this than what I've been doing: pull new month's data to separate file, visually scan rows to see which fields have changed and then copy/paste the changed line (or cell) into the master file. The volume isn't all that large - approximately 200 rows of data monthly. I'm trying to avoid re-pulling all data every time, since there is an occasional correction that has to be made in the worksheet for certain tickets - if I re-pulled all data, I would have to keep track of which records have to be corrected each time. I'm not sure what other options there might be. I'd appreciate any advice.
Hi Linda
When you amended the data in your worksheet, you should have also amended the same in the ticketing system.
This will then allow you to download all the data without any issue.
Sunny
Hi Linda,
That sounds like the perfect job for Power Query. With Power Query you can get the data from your 3 source files and consolidate it into your 'master file'. Power Query retains a connection to the source files so you can just Refresh the connection and pull in the updates to your master file automatically.
If you need to clean/filter/modify/add columns to the data before you can report on it then this can also be done with Power Query. You tell Power Query what to do once, and then when you refresh it performs the same steps on any new data. Much like you might use a Macro or VBA to automate a process.
It's a bit difficult to describe all the steps here but I cover them in detail in my Power Query course: https://www.myonlinetraininghub.com/excel-power-query-course
Please let me know if you have any questions.
Mynda
Thank you. I will certainly take a look at that.
We can enjoy the power of Power Query..
