Searching Multiple Rows/Columns to Populate Column

[Tom Wasylkiewicz]

Hi, was hoping to get some help across comparing data from multiple columns with the goal of populating Column D. Here is an example:

 

Column A Column B Column C Column D

Oper1 9/1/1990

Oper2

Oper3 3/1/2021

Aper1 9/1/1990

Aper2 3/1/2021

Aper3

I want to find for "Oper" and "Aper" (always 4 digit string on left in column A) which event (1, 2, or 3) is relevant (dictated by most recent date). Hoping someone can help me identify a function that will search the rows for "Oper" and look at the most recent date in Columns B and C. Ideal output in column D would be "Relevant" for Oper 3 and Aper 2, and "Irrelevant" for the other 4 rows. Thanks guys.

[Fabian Duerr]

You can work with some additional helper columns:

 

ColA_group:

left([ColA],4)MaxDate:

Max([ColB],[ColC])Result:

If([MaxDate]=Max([MaxDate]) OVER ([ColA_group]),[MaxDate],NULL)

[Tom Wasylkiewicz]

Output for Column D below. Basically, in row 1 (Oper1 row), the function in Column D should search for "Oper" (left 4 elements of string in Column A) in all other rows in Column A, and find which row has the latest date in columns B and C. If that iteration of "Oper" is the latest, Column D returns a string "Relevant". If another "Oper" row has in columns B or C a more recent date, then Column D in row 1 should return as "Irrelevant". 

 

Column A    Column B       Column C    Column D

 

Oper1           9/1/1990                              Irrelevant

 

Oper2                                                       Irrelevant

 

Oper3          3/1/2021                               Relevant

 

Aper1           9/1/1990                              Irrelevant

 

Aper2                                3/1/2021         Relevant  

 

Aper3                                                      Irrelevant

[Fabian Duerr]

Please use my solution below. Just change the last calculated column to

 

If([MaxDate]=Max([MaxDate]) OVER ([ColA_group]),"Relevant","Irrelevant")