Index/Match/Max finds Header info... but how do I deal with duplicate values?

[chonchos]

First, some background on what I have before we get into what Im trying to do.

The Sheet pulls in data from our external database that details different repair orders. Each row has Company Name, Entry Date, Repair Order Number, Notes, etc.

This sheet is used to assist in managing these orders. We want to make sure that we request the appropriate information at the appropriate time. For example, if we just sent a part off to a shop 2 days ago, we don't want to start hassling them for a quote when the part likely hasn't even been delivered to their door yet. Therefore, the process we follow is laid out in Excel the same way the process flows in real life. We request the quote, we receive the quote, approve or deny quote, request ETA for delivery, and input expected return date when we receive it. If the part is already on site, we don't need to do anything.

Right now, the sheet is complicated and convoluted but I'm doing the best with what I've got. Here is an example of what I'm working with:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]AE[/TD]
[TD="align: center"]AF[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ENTRY_DATE[/TD]
[TD="align: center"]CURRENT_STATUS[/TD]
[TD="align: center"]WHAT_TO_DO[/TD]
[TD="align: center"]ARRIVED ON SITE[/TD]
[TD="align: center"]Quote Requested[/TD]
[TD="align: center"]Quote Received[/TD]
[TD="align: center"]Quote Not Approved[/TD]
[TD="align: center"]Quote Approved[/TD]
[TD="align: center"]ETA Requested[/TD]
[TD="align: center"]ETA Status Update[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5/8/18[/TD]
[TD="align: center"]QUOTE NOT APPROVED[/TD]
[TD="align: center"]Move Quote[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/22/18[/TD]
[TD="align: center"]5/23/18[/TD]
[TD="align: center"]5/23/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]4/15/18[/TD]
[TD="align: center"]QUOTE RECD[/TD]
[TD="align: center"]Evaluate Quote[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/24/18[/TD]
[TD="align: center"]5/16/18[/TD]
[TD="align: center"]5/16/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2/1/18[/TD]
[TD="align: center"]ETA REQUESTED[/TD]
[TD="align: center"]Send Another ETA Request[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/20/18[/TD]
[TD="align: center"]3/20/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/20/18[/TD]
[TD="align: center"]5/7/18[/TD]
[TD="align: center"]5/7/18[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5/2/18[/TD]
[TD="align: center"]QUOTE APPROVED[/TD]
[TD="align: center"]Request ETA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/7/18[/TD]
[TD="align: center"]5/7/18[/TD]
[TD="align: center"]5/7/18[/TD]
[TD="align: center"]5/11/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]4/24/18[/TD]
[TD="align: center"]QUOTE APPROVED[/TD]
[TD="align: center"]Request ETA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/4/18[/TD]
[TD="align: center"]5/5/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/5/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/5/18[/TD]
[/TR]
</tbody>[/TABLE]

Now, basically this is how it works. All of the date fields (Columns Z through AF) are populated by searching the Notes field (not shown in example) using a nifty VBA script that I actually got assistance with on this wonderful site. Then, CURRENT_STATUS (Column Q) is a formula that finds the max value across the row and outputs the associated Header.

=IF(ISNA(IF(Z2="",(INDEX($AA$1:$AF$1,0,MATCH(MAX($AA2:$AF2),$AA2:$AF2,0))),"ARRIVED ON SITE")), "NO MOVEMENT", IF(Z2="",(INDEX($AA$1:$AF$1,0,MATCH(MAX($AA2:$AF2),$AA2:$AF2,0))),"ARRIVED ON SITE"))

This formula works great... EXCEPT when there are duplicate values.

So, using the example above, Row 3 has the date 5/16 under both "Quote Received" and "Quote Not Approved". It Returns "Quote Recd" under Current Status. However, we have already received and evaluated this quote. Ideally, in order to keep up with the order in which our processes go, I would like the return value under "Current Status" to be "Quote Not Approved" since it is the last date of movement for this line item. Similar other examples are shown above.

I have racked my brain, googled to no end, and just started trying random adjustments to the "Current Status" formula but I'm not getting the result I'm looking for.

I know this is THE BEST board for Excel problems... so here I am waving the white flag. Please help me!

 

[Eric W]

How about:

=IF(Z2<>"",Z$1,IF(MAX(AA2:AF2)=0,"NO MOVEMENT",LOOKUP(2,1/(AA2:AF2=MAX(AA2:AF2)),AA$1:AF$1)))

 

[chonchos]

Eric W you are officially my hero.

That works beautifully.

See, this is why I come here. Spend 5 days in agony convincing myself it must be impossible and then I get the perfect, elegant answer 5 minutes after posting a thread.

Now, let me continue on with this sheet and figure out what my next impossible task is. :D

Thank you! You're a gentleman and a scholar!

 

[Eric W]

Glad to help!