VBA: Find latest date in row and return header and data

[omnivl]

I have a header and rows of data that looks like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Contract Number[/TD]
[TD]Comments[/TD]
[TD]Advertised[/TD]
[TD]Closed[/TD]
[TD]Evaluated[/TD]
[TD]Contract Value[/TD]
[TD]Awarded[/TD]
[/TR]
[TR]
[TD]Executed[/TD]
[TD]1[/TD]
[TD]waiting on david[/TD]
[TD]20/11/17[/TD]
[TD]30/11/17[/TD]
[TD]1/12/17[/TD]
[TD]$50,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]2[/TD]
[TD]Finalising contract[/TD]
[TD]10/11/17[/TD]
[TD]16/11/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]3[/TD]
[TD]not sure[/TD]
[TD]5/11/18[/TD]
[TD]10/11/18[/TD]
[TD]13/11/18[/TD]
[TD]$21,000[/TD]
[TD]15/11/18[/TD]
[/TR]
</tbody>[/TABLE]


Firstly i need to look at status, if the status is "Executed" then ignore and go to the next row. It then needs to find the latest date entered for that row, in row 2 this would be 16/11/17, it needs to then put into an array the contract number "2", then comments "Finalising contract" then the date 16/11/17 followed by the latest date header Closed

Once they are in an array i need to print them to sheet1 in the workbook for example


[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract Number: 2, Closed - 16/11/17, Finalising contract[/TD]
[/TR]
[TR]
[TD]Contract Number: 3, Awarded - 15/11/18, not sure[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be really appreciated!

Thanks
Ben

 

[Keebellah]

Hi, how about attaching a sample file? Makes it easier than retyping and explaining

 

[omnivl]

Hi Keebellah

Thanks for your interest in helping me, got a bit confused tried worksheet function max, arrays but could not figure it out.
Attached filehttps://we.tl/t-SUke5U9d96

 

[Keebellah]

Which columns are the columns you want to check the date?
There is a whole range with dates and some non-date values in between.
I adde a column with this formula but since I don't know the whole range:

=IF($A2="Executed","",MAX(AQ2,AO2,AN2,AM2,AK2,AJ2,AI2,AH2,Q2,R2,S2,T2,U2,V2,W2))