Flagging the latest final transaction as LIVE if it appears in a range more than once...

[HOGGL]

Hi,

I feel like this should be really easy with an if statement and a concatenate but the max on the date is throwing me off.

I'm looking to add a formula to column F that determines the line is "LIVE" or "INACTIVE"

So If "Customer & Code & Product & Status" appear in the list of data more than once then flag the most recent (using the Date column D) as "LIVE" and the others as "INACTIVE".

Thanks in advance

	A	B	C	D	E	F
1	CUSTOMER	CODE	PRODUCT	DATE	STATUS	LIVE
2	CBOO600	7200	1604	23/04/2017	FINAL
3	CBOO600	7200	1604	01/05/2017	FINAL
4	CBOO600	7200	1604	23/05/2017	PROVISIONAL
5	CBOO600	7300	1604	25/01/2017	FINAL
6	CBAR100	7200	1501	01/09/2017	PROVISIONAL
7	CBAR100	7200	1604	23/05/2017	FINAL

<tbody>
</tbody>

 

[Eric W]

I have a few options for you:

	A	B	C	D	E	F	G	H
1	CUSTOMER	CODE	PRODUCT	DATE	STATUS	LIVE
2	CBOO600	7200	1604	23-Apr	FINAL	Inactive	Inactive	Inactive
3	CBOO600	7200	1604	5/1/2017	FINAL	Inactive	Inactive	Inactive
4	CBOO600	7200	1604	23-May	PROVISIONAL	Live	Live	Live
5	CBOO600	7300	1604	25-Jan	FINAL	Live	Live	Live
6	CBAR100	7200	1501	9/1/2017	PROVISIONAL	Live	Live	Live
7	CBAR100	7200	1604	23-May	FINAL	Live	Live	Live

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula F2 =IF(D2=MAXIFS(D:D,A:A,A2,B:B,B2,C:C,C2),"Live","Inactive") H2 =IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2),"Live","Inactive") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G2 {=IF(D2=MAX(IF($A$2:$A$10=A2,IF($B$2:$B$10=B2,IF($C$2:$C$10=C2,$D$2:$D$10)))),"Live","Inactive")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you have Excel 2016 or newer with the MAXIFS function, the F2 formula should work for you.

If you don't have MAXIFS, the G2 array formula is equivalent.

If you don't like array formulas, and the dates are entered in order, then the H2 formula should work. It just basically marks the lowest combination of Customer/Code/Product as Live, and everything above it as Inactive.