Assistance with aligning two excel file data sheets

Question

Hi all,

I have two excel sheets I'm bringing in and processing. One is timestamped time series data (A) and the other (B) is a timestamped dataset that only contains data of when an event has been fired. What I need to do is copy the entry from B into A when the filename column is the same, and when the timestamp column is the same. The two sheets currently are not aligned so I can't do a simple equation to check if the columns are the same.

I tried to do this with the join tool but it doesn't function as I expected. I unfortunately can't upload the data as it's proprietary in nature.

Thank you!

RobertOdera · Answer

Hi, @grantmorfitt

Please check out the below.

Please mark it as an acceptable solution if it works for you.

Cheers!

Aligning_ForGrantmorfitt.yxzp

grantmorfitt · Answer

Understood. I'm attaching them to this.

So dataset B has events with a timestamp and a filename in which they occur. Meanwhile, dataset A has many time stamps and will contain many different file names. I need to be able to add the events that occur and match(timestamp and event) from dataset B and add it to dataset A. For example, the single version of "Event type 3" occurs in the dataset A towards the end of the file. I want to add the additional column that contains that information that matches from sheet B and add it to sheet A.

outputB.csv

outputA.csv

Felipe_Ribeir0 · Answer

Hi @grantmorfitt

I believe that if you make two excel files with the real column names but with some dummy data (fake timestamps, filename, etc) you have more chances of having a correct answer.