VLOOKUP and return complete row but with certain conditions

[Alfie092]

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

[FlameRetired]

I don't think I thoroughly understand.

Though there are no cases of this in your upload suppose there are multiple instances of an Event ID where a later date has the higher IR code priority.

What row would you want returned?

[Alfie092]

I don't think I thoroughly understand.

Though there are no cases of this in your upload suppose there are multiple instances of an Event ID where a later date has the higher IR code priority.

What row would you want returned?

Hi, thank you for the reply and sorry if I haven't been clear!

In regards to your comment about multiple instances of an Event ID where a later date has the higher IR code priority. Yes, in my full spreadsheet which is very large to attach, there are instances of this.

Therefore, what I want to return is BOTH the earliestdate of a specific Event ID and the highest IR code priority listed in any of the rows for a specific Event ID.

Do you believe this can be done? I would really appreciate yours or anybody else's help please

[FlameRetired]

Thank you.

Working on it.

[Alfie092]

Thank you.

Working on it.

Thank you so much! I am extremely appreciative of it!

[FlameRetired]

In regards to your comment about multiple instances of an Event ID where a later date has the higher IR code priority. Yes, in my full spreadsheet which is very large to attach, there are instances of this.

Please upload another file with a representative mix of all the conditions.

Edit Also where does IR code 52 (cell L31) rank relative to the others?

[Alfie092]

Please upload another file with a representative mix of all the conditions.

Edit Also where does IR code 52 (cell L31) rank relative to the others?

Hi, please see the attached file, I have amended row 7 from the first table to include a representative mix of all the conditions.

This file shows in row 7 an Event ID (211680) which contains a IR code of AOG, however, it is only returning the IR code of 41 from the first row (row 5) in which Event ID 211680 is found. In cell AA5, I want it to show "AOG".

Also, the additional table on the right includes the date of 02/10/2018, which is from the first row (row 5) in which Event ID 211680 is found. However, this date is not the earliest date as the date of 01/10/2018 found in row 6 is the earliest date. Therefore, cell R5 should display "01/10/2018".

I have included an image below (from the attached file), which includes highlighting in orange the row/Event ID I am looking at and have highlighted the 2 cells in red (R5 and AA5) in which the values returned are incorrect.


Capture8.PNG

In regards to IR code 52, this ranks lowest in the priority order (however, I may have to change this later myself )

I hope this is enough information for you to help me please? Do let me know if you require anything else. I really appreciate your help!

Thanks

[FlameRetired]

Therefore, what I want to return is BOTH the earliestdate of a specific Event ID and the highest IR code priority listed in any of the rows for a specific Event ID.

Also, the additional table on the right includes the date of 02/10/2018, which is from the first row (row 5) in which Event ID 211680 is found. However, this date is not the earliest date as the date of 01/10/2018 found in row 6 is the earliest date. Therefore, cell R5 should display "01/10/2018".

So where do you want 02/10/2018 to go? From the first quote I have worked with the understanding you want both returned. Now I am not so sure.

What am I missing?

[Alfie092]

So where do you want 02/10/2018 to go? From the first quote I have worked with the understanding you want both returned. Now I am not so sure.

What am I missing?

Hi, from the right table created from my formula which partially works, I am currently using the formula=VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE). However, the date column (column R) and the IR code column (column AA) are incorrect, as my current formula appears to be only picking up the values within the first row of where a specific Event ID is given within the table on the left of the attached file.

For the date column (Column R), I just want the earliest date returned, so in this instance it's 01/10/2018 for event ID 211680 and for the IR Code column (column AA), I want the highest priority IR code displayed only, which is AOG.

When I initially stated I want both returned I was referring to wanting BOTH the earliest date returned and the highest IR code priority in the following order: LOM, DAM2, DAM1, AOG, 41, 43, 52

As for your question about where I want 02/10/2018 to go. I don’t want this date returned as currently shown on my spreadsheet where the cell is highlighted in red as this is not the earliest that for Event ID 211680. Instead I want this cell to return 01/10/2018 as this is the earliest date for Event ID 211680.

Apologies for any confusions caused! And I hope I have clarified to you what I want now. Please let me know if you are still unclear on anything

[Alfie092]

Sorry for being a pain but I would really appreciate yours or anybody's help in regards to this please. Thanks

[Alfie092]

Is someone kindly able to help me please, I am trying to figure it out myself but I am really struggling

[FlameRetired]

Patience Alfie092.

The forum is staffed by volunteers = we work for free. Been working on this; some are asleep (other side of the planet ); some are at work; others working on other threads.

With that thought in mind:
If you feel your thread is not getting adequate attention we request you wait at least 24 hours from your unanswered posting before posting again. This "bumps" your thread to the top of the queue.

[FlameRetired]

The attached is with the understanding that unique Event IDs in column P are returned by using the 'Remove Duplicates' feature.

Also you should avoid whole column references in the Table1 formulas. If you notice it takes a while to do any calculations or open your file. I changed them to just the ranges used. If you need to "future proof" the ranges use the structured table references. They resize according to data.

In Q2 find this formula copied across and down until you get blanks.

=IF(OR($P2="",Q$1=""),"",INDEX(Table1,AGGREGATE(15,6,(ROW(Table1)-MIN(ROW(Table1))+1)/((AGGREGATE(15,6,Table1[[Date]:[Date]]/

($P2=Table1[Event ID]),1)=Table1[[Date]:[Date]])*($P2=Table1[Event ID])),1),MATCH(Q$1,$A$1:$L$1,0)))

[Alfie092]

The attached is with the understanding that unique Event IDs in column P are returned by using the 'Remove Duplicates' feature.

Also you should avoid whole column references in the Table1 formulas. If you notice it takes a while to do any calculations or open your file. I changed them to just the ranges used. If you need to "future proof" the ranges use the structured table references. They resize according to data.

In Q2 find this formula copied across and down until you get blanks.

=IF(OR($P2="",Q$1=""),"",INDEX(Table1,AGGREGATE(15,6,(ROW(Table1)-MIN(ROW(Table1))+1)/((AGGREGATE(15,6,Table1[[Date]:[Date]]/

($P2=Table1[Event ID]),1)=Table1[[Date]:[Date]])*($P2=Table1[Event ID])),1),MATCH(Q$1,$A$1:$L$1,0)))

Thank you so much for your help! This has answered what I am looking for! And apologies for not being patient enough!

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback, added rep and marking your thread Solved.