Conditional Formatting with Duplicate Rows to Identify Most Recent Value

[rac241985]

I have multiple rows with identical values in 3 columns and the 4th column has a unique value based on the date when it is sold. Can someone please help me in creating a 5^th column which will show me based on conditional formatting value which was sold earlier and which was sold more recently? If it is sold on a previous date then I want it to show the output as “Old” else the output should be “new”. Additionally, if there are no duplicate rows, then the desired output will always be “New”.

Product	Country	Units Sold	Date (mm/dd/yy)	Desired Output
Apple	Taiwan	100	1/12/15	Old
Apple	Taiwan	100	2/25/15	New
Apple	China	50	1/27/16	New
Samsung	Taiwan	100	2/25/15	New
Samsung	Taiwan	100	1/25/14	Old

<tbody>
</tbody>

Any help will be appreciated!

Thanks!

 

[markmzz]

Hi!

Try the formula below in E2 and copy down

=IFERROR(IF(LARGE(IF(MMULT(--($A$2:$C$6=A2:C2),{1;1;1})=3,$D$2:$D$6),2)=D2,"Old","New"),"New")

Markmzz

 

[markmzz]

If you have more then two values, try this:

=IF(LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)<>D2,"Old","New")

Or

=IF(LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)=D2,"New","Old")

Markmzz

 

[rac241985]

Thanks a lot Markmzz!! It works like charm. However, since I am relatively new, can you explain how this code works? It will be really beneficial to understand rather than just copy paste . Once again! Thanks a ton and appreciate your help.

 

[markmzz]

Thanks a lot Markmzz!! It works like charm. However, since I am relatively new, can you explain how this code works? It will be really beneficial to understand rather than just copy paste . Once again! Thanks a ton and appreciate your help.

Lets look at the row 2 and the formula there - step-by-step:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Country	Units Sold	Date	Output		Lets look at row 2				Formula	=IF(LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)=D2,"New","Old")
2	Apple	Taiwan	100	12/01/2015	Old		Step-By-Step
3	Apple	Taiwan	100	25/02/2015	New						{1;1;1}
4	Apple	China	50	27/01/2016	New						1
5	Samsung	Taiwan	100	25/02/2015	New						1
6	Samsung	Taiwan	100	25/01/2014	Old						1
7							--($A$2:$C$7=A2:C2)	1	1	1	3	MMULT(--($A$2:$C$7=A2:C2),{1;1;1})
8								1	1	1	3
9								1	0	0	1
10								0	1	1	2
11								0	1	1	2
12
13											TRUE	MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3
14											TRUE
15											FALSE
16											FALSE
17											FALSE
18
19											12/01/2015	IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7)
20											25/02/2015
21											FALSE
22											FALSE
23											FALSE
24
25											25/02/2015	LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)
26
27											FALSE	LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)=D2
28
29											Old	=IF(LARGE(IF(MMULT(--($A$2:$C$7=A2:C2),{1;1;1})=3,$D$2:$D$7),1)=D2,"New","Old")
30
***			*******			**		***	***	***		***************************************************************	**

<tbody>
</tbody>

I hope that the table above helps.

Markmzz

 

[rac241985]

HI Mark,


Thanks for the breakdown! This is helpful. However, can you please also explain me what "--" stands for in the MMULT function you have used ?

Regards,

 

[markmzz]

HI Mark,


Thanks for the breakdown! This is helpful. However, can you please also explain me what "--" stands for in the MMULT function you have used ?

Regards,

Yes. The -- is the same -1*-1 that is the same 1. So --=-1*-1=1. So you can use in the formula this too: 1*($A$2:$C$7=A2:C2) or -1*-1*($A$2:$C$7=A2:C2)<strike></strike>

I hope that helps.

Markmzz

 

[markmzz]

HI Mark,

Thanks for the breakdown! This is helpful. However, can you please also explain me what "--" stands for in the MMULT function you have used ?

Regards,

Yes. The -- is the same -1*-1 that is the same 1. So --=-1*-1=1. So you can use in the formula this too: 1*($A$2:$C$7=A2:C2) or -1*-1*($A$2:$C$7=A2:C2)<strike></strike>

I hope that helps.

Markmzz

One more thing: The -- will transform TRUE into 1 and FALSE into 0. In the link below you have more information about TRUE and FALSE in Excel.

https://www.deskbright.com/excel/true-false-excel/

Here is more one step-by-step table.

	A	B	C	D	E	F	G	H	I	J	K
1	Product	Country	Units Sold	Date	Output		($A$2:$C$7=A2:C2)	TRUE	TRUE	TRUE
2	Apple	Taiwan	100	12/01/2015	Old			TRUE	TRUE	TRUE
3	Apple	Taiwan	100	25/02/2015	New			TRUE	FALSE	FALSE
4	Apple	China	50	27/01/2016	New			FALSE	TRUE	TRUE
5	Samsung	Taiwan	100	25/02/2015	New			FALSE	TRUE	TRUE
6	Samsung	Taiwan	100	25/01/2014	Old
7							--($A$2:$C$7=A2:C2)	1	1	1
8							-1*-1*($A$2:$C$7=A2:C2)	1	1	1
9							1*($A$2:$C$7=A2:C2)	1	0	0
10								0	1	1
11								0	1	1
12
***			***********			**	**********************	******	******	******	**

<tbody>
</tbody>

Markmzz