VLOOKUP and return complete row but with certain conditions

Hi,

I need some help please

I have several rows where a specific Event ID (Column A) occurs multiple times. What I want to do is have all the key information on just 1 row without having the Event ID being displayed on multiple rows.

As shown in the below image and attached spreadsheet, in column P, I have for removed the duplicates for each of the IDs and in cell Q2 I have used the formula =VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE) and dragged across + down to populate/create an additional table which I will be working on.

Capture7.PNG


This additional table appears to partially return what I am looking for. However, I need a formula to return the following on a single row for a specific Event ID:

1) Based from the Date column (Column C) I want to return the earliest date shown of a specific ID within the additional table created on the right. For example Event ID 211855 has dates, 01/10/2018 and 02/10/2018 across multiple rows. I just want 01/10/2018 to be returned as it's the earliest date.

2) From the IR Code column (Column L) I want to return the IR code for a specific Event ID in terms of "priority" being shown in the following order: LOM, DAM2, DAM1, AOG, 41, 43. So for example, if in any of the rows for a specific Event ID, "LOM" does not appear, then it looks to return "DAM2", if that does not appear in any of the rows for a specific Event ID and DAM1 does, then "DAM1" is returned, as it's the highest priority code being shown. (Tried to explain this as simple as possible! )

3) From the additional table I have created, the formula I have used returns all the columns, however, I only want to return specific columns. I want to return columns: B, C, D, G, H, I, J, K, L and M. I have tried just deleting the columns from the additional table created but then it ruins the whole table and returns incorrect values.

I would appreciate it if anyone could help me out please

Thanks