Hi,
I have a file that collects the hours worked by different people. The data for the individuals is entered on separate rows however there is some information that is entered above it that is common to all. I would like to combine the common data so that is shows in each row next to the individuals hours entered.
This is a task that needs to be repeated regularly. The layout of the initial file can't be changed. I am hoping that I can create a query to do this but I am unsure how.
In the attached file the "TS" sheet is the initial file layout and as stated can't be updated. The second sheet in the file, "Result", is what I am aiming for except I have added in an extra row, row 1, which indicates the source of the data from the "TS" sheet.
As a second stage to this I would ideally like to be able to combine all files in a folder to create one data table but let's see if the above can be done first!
Thanks
Bax
Hi Bax,
There are probably loads of ways to tackle this. In the attached file I've created 3 queries; the first captures the header information the second captures the items and the third, 'Tabular Data' merges them together into the desired output.
If you want to learn Power Query you might consider my course here: https://www.myonlinetraininghub.com/excel-power-query-course
Mynda
Hi Mynda,
Thanks for your input. That works well but is there any way to do something similar without putting the data in the first sheet in to a table?
In reality what actually happens is that these reports are filled in weekly by many different people. They are then saved to a shared folder for the current week. I was hoping that what I could do was create a query that picked up all of the files in the folder and combined them in to one table.
The file format is in use by many people and therefore changing it is a major job so I was hoping that maybe the query option could be applied to the file as it is. Do you think this is possible?
Regards
Bax
You don't have to format the data in a table. You can bring in a named range or a sheet, but your data is too messy to be able to bring in every file from a folder in one go.
You can import one files at a time and then merge them.
Might be easier to teach your users to use a new template that better suits your needs.
Have you considered using Excel Survey for your timesheets? It puts the data into a nice tabular format without you having to lift a finger and your users can enter the data online. More info on Excel Survey here: https://www.myonlinetraininghub.com/excel-surveys-an-easy-way-to-collect-data
Mynda
Hi Mynda,
Thanks again. I am aware of the Survey option (although I can't seem to get the date to format to the UK style!). It is just that the use of the current method is "ingrained" and I know implementing a change will be an issue so I was just hoping that PQ would be my saviour.
Thanks
Bax
Ah, yes, the US date format in the survey is a pain. The only suggestion I have is to ask them to input the day, month and year in separate fields and then in the Excel file you can create a date column from the 3 fields using the DATE function.
Or, force them to change!
Mynda
