Return value for 1st, 2nd, 3rd, 4th occurance

Bany26

New Member
Joined
Apr 10, 2018
Messages
4
Dear all,

could you please help me return different notes for old and new entries considering the date (column A)? I would need for newest row insert note ''valid'' in column C. And for rest - older rows, I would need note ''old'' in column C.



DateNameresult
20.9.2016Kevinold
7.2.2017Kevinold
8.5.2017Kevinold
10.4.2018Kevinvalid
22.3.2018Alexanderold
10.4.2018Alexandervalid
10.4.2018Mikevalid

<colgroup><col><col><col></colgroup><tbody>
</tbody>


thank you in advance
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,148
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
this works if column A is true dates, (e.g. replace the . with - or /);

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Name</td><td style=";">result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">20/09/2016</td><td style=";">Kevin</td><td style="background-color: #E2EFDA;;">old</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">07/02/2017</td><td style=";">Kevin</td><td style="background-color: #E2EFDA;;">old</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">08/05/2017</td><td style=";">Kevin</td><td style="background-color: #E2EFDA;;">old</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">10/04/2018</td><td style=";">Kevin</td><td style="background-color: #E2EFDA;;">valid</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">22/03/2018</td><td style=";">Alexander</td><td style="background-color: #E2EFDA;;">old</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">10/04/2018</td><td style=";">Alexander</td><td style="background-color: #E2EFDA;;">valid</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">10/04/2018</td><td style=";">Mike</td><td style="background-color: #E2EFDA;;">valid</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IF(<font color="Blue">MAX(<font color="Red">IF(<font color="Green">$B$2:$B$8=B2,$A$2:$A$8</font>)</font>)=A2,"valid","old"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,148
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,108,621
Messages
5,523,947
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top