Combining rows from 2 sheets that matches using VBA/macro

[Rory123908]

Hello everybody & thankyou in advance for considering my problem! I have 2 sheets. Sheet 1 (26 columns by 85896 rows) and Sheet 2 (15 columns by 43201 rows).

Both of the sheets contain different variables with different values however they both share similarities in the time column (column A). For example, Sheet 1 records data almost every 1 second of a day while Sheet 2 records data every 2 seconds of the day. I am looking to create a macro so that I am able to compare the time column in sheet 1 to sheet 2, if it matches, I would like to copy the row of data from both sheets and put it into a new sheet 3. The result would be a fresh sheet (sheet 3), that contains the data from both tools/sheets when they are recorded at the same time. 

Currently I am really struggling with this, any help would be really really appreciated! Thanks so much for having a look at this, and if you can help, please let me know! Thanks again!

[#2]

@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.

[#3]

Hi there, many thanks for your response! I have uploaded a file representing my problem, 100 rows long. As you may see, each sheet represents the responses from a tool that records data. They were both recorded/taken at the same time, but at different frequencies and with the occasional missing data point. I am looking to combine the responses into a new sheet (sheet 3), so that each row on sheet 3 represents a point where all variables share recordings at the same time.

[#4]

@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.

https://exceloffthegrid.com/power-query-introduction/ 

[#5]

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?

[#6]

@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.

[#7]

Hi again, I've spent the day looking through how PQ could apply to my problem, and your correct, it does seem to fit very nicely here! I've managed to apply what you have done to my full dataset and can now visualise properly, so thank you very much for advising this. The process in which I did this was somewhat manual, is there anyway that I can automate this? for example, press a button and it would create the queries, merge them, show all columns etc, so that the users will not have to know PQ, but instead can just press the button or something similar to this to save the PQ process? Thanks again, learning new things about excel/VBA/PQ everyday, its great!

[#8]

@Rory123908 I'm sure you could automate PQ in conjunction with VBA, but that would go beyond the scope of this "Help-forum", I think. And I'm not really comfortable with VBA.