VBA Code to Assemble data horizontaly

[Jeev125]

Hi All,

Need help in Developing a vba code. I tried Power Query but could Pivot or Group using multiple filters.

Can somebody create a logic to compare data vertical to horizontal. I have attached my Excel for reference.

Raw Data Is the CVS Dump.

I want to assemble to Sample File.

I tried First and last event Vlookup, but i get results in different lines. Power Query i couldn't find a function which assemble like the sample data (which is the output file i need). Need help in resolving this issue. The current match up is purely done manually.

Filter required while setting up the raw data. 

Column F: Fault (Only include Moving Status)

Column G: Fault Type 1 (Stopped & moving) is the filter criteria.

[#2]

@Jeev125 Don't know what you tried in PQ, but I created something, based on the RawData that looks like your sample. See attached. Perhaps it helps.

[#3]

@Riny_van_Eekelen 

Hi Riny, i tried this solution before but my data has some notification without failure. what i am trying to match is if Date & Time-Location-System-Equipment with moving status is STOPPED then same event should come with different time but with Moving.

My data is through a system generated so i have no control, I have attached Quarterly raw data wherein i have 3075 line with moving status Stopped and 3196 Moving. i want to match stop lines with moving and remove unmatched. 

[#4]

@Jeev125 Don't really know what I'm looking at. Can you explain in an example how you would match events and report them if you had to do it manually, using the data that you uploaded in your previous post?

[#5]

@Riny_van_Eekelen The logic whihc i used is First Stop and First Movingwhich ever is closest

Example : Stop 12:30:00   

Moving 12:31:00

Moving 12:32:00

Moving 12:35:00

We select the first Moving 12:31:00. if Location- System- Equipment match.

I have attached example.

1st Tab list- Events history with Filters thats the filter i use.

2nd Tab- Result which needs to be in this pattern.

There will be events which wont have both the events in that case we need to remove it. This were i am struggling in Power Query. 

[#6]

@Jeev125 Changed the approach altogether and didn't try to replicate your output. See if the attached PQ solution contains the logic you are after. Checked several events against the raw data. Seems to work OK, but you need to assure this yourself.

[#7]

@Riny_van_Eekelen 

Used your solution but when you use fill option some instance it copies date and time from other reference which is not matching to actual data. Now i need to get First occurrence and last occurrence of the same event to get exact match. Considering the logic Alarm as start & Normal as Finish. Any idea on that ?

[#8]

@Riny_van_Eekelen 

Also i tried using List.Max or List.First Function but i think it only take max or min values for the largest or smallest value. Somehow i must be  able to match Alarm time for that particular day location time to next Normal Date & time event only.