tag the comment based on criteria

[nikhil0311]

Book1
	A	B	C	D	E
1	ID	Country	Time Period	ID+Country	Comment
2	44	Malaysia	2013	44Malaysia	Old 2013
3	22	Malaysia	2013	22Malaysia	Old 2013
4	99	Malaysia	2013	99Malaysia	Old 2013
5	33	Hong Kong	2014	33Hong Kong	New 2014
6	44	Malaysia	2014	44Malaysia	Old 2013
7	55	Hong Kong	2014	55Hong Kong	New 2014
8	33	Hong Kong	2015	33Hong Kong	old 2014
9	66	China	2015	66China	New 2015
10	33	China	2015	33China	New 2015
11	22	Japan	2016	22Japan	New 2016
12	66	Japan	2016	66Japan	New 2016
13	66	China	2016	66China	Old 2015
14	22	Japan	2017	22Japan	Old 2016
15	55	India	2017	55India	New 2017
16	11	India	2017	11India	New 2017
New Record

Column A has ID. Column B has Country Column C has time period column d has ID+ Country and Column E is the final output.


I want to update column E i.e. comments based on comparision of 2 time periods.For ex

(compare 2014 ID + Country with 2013 ID + Country. If ID+Country is not present in prior year then its "New 2014" else "Old 2013".


Criteria

Compare 2014 Vs 2013 If ID+Country in 2014 is not Present in 2013 then comment should be "New 2014" else "Old 2013"
or
Compare 2015 Vs 2014 If ID+Country in 2015 is not Present in 2014 then comment should be "New 2015" else "Old 2014"
or
Compare 2016 Vs 2015 If ID+Country in 2016 is not Present in 2015 then comment should be "New 2016" else "Old 2015"
or
Compare 2017 Vs 2016 If ID+Country in 2017 is not Present in 2016 then comment should be "New 2017" else "Old 2016"

for 2013 = "-"

let me know if you have any questions!

 

[DRSteele]

I think I understand correctly, and if I do then this should work. Copy E2 down as necessary. Note that your rows 2,3 and 4 should show "New 2013" according to your declared rule because there is no data for 2012, but I put in a condition that reports Old 2013 if the year is 2013.

	E
2	Old 2013

<tbody>
</tbody>

Sheet35​

Array Formulas Cell Formula E2 {=IF(C2=2013,"Old "&C2,IF(MAX((D2=$D$2:$D$16)*((C2-1)=$C$2:$C$16)),"Old "&C2-1,"New "&C2))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>