Forum Discussion
Combining rows from 2 sheets that matches using VBA/macro
Rory123908 I'd suggest you forget about VBA and look into PowerQuery in stead. Quite easy, I dare to say, though difficult to demonstrate with some real data to play with. Can you upload a file that represents the actual structure of your real sheets/tables? Say, a hundred rows of data in each or so, with matching time-stamps between the two. Remove any private and/or confidential information, though.
Rory123908 Thanks! Attached file now includes a sheet with records from both tables, every 2nd second. Is that what you had in mind. The PQ steps are relatively simple but it needs getting used to if you have never worked with it before.
This would be a good starting point to learn more about it.
- Hi again, many thanks for helping. There are 2 points that I should have made. Firstly, I am creating a system so that our engineers are able to process their data without software knowledge. The intention of a macro was that all they have to do is press a button and then they would get this combined sheet, ready to upload into power BI and use for visualisations of our data. The data I am presenting to you is from 1 job/operation, but there will be many different jobs/operations over time and the process will have to be repeated. I'm not too knowledgable on PQ, however if there is a requirement to manually implement it every time, this is not ideal. additionally, I should have explained the nature of the data, this was not obvious in my example 100 rows. There are missing & duplicated time points in the sheet 1. For example rows 309 and 310 both show the time as 00:05:09 and row 467 shows 00:07:47 while row 468 shows 00:07:49. The data in sheet 2 is consistently taken every 2 seconds however. Please refer to the initial problem, you will see there is not exactly double the amount of entriesin sheet 1 than there is sheet 2. Do you think it is possible to account for this while still being able to merge the sheets?
Rory123908 If you insist on VBA, I can't help. But your description is definitely suited for Power Query. Once set-up correctly, it can be used over and over again. The user doesn't even have to see the individual files you currently have in Sheet1 and Sheet2. All they would need to learn is to right-click in the merged. But perhaps I underestimate the complexity of your issue.