Which formula to use in B2 Sheet 1 to get minimum value / oldest date and C2 to get maximum value / newest date
Taking into consideration that ID rows can't be determined hence they might be 3, 4, or more than that
<p>Which formula to use in B2 Sheet 1 to get minimum value / oldest date and C2 to get maximum value / newest date</p>
<p>Taking into consideration that ID rows can't be determined hence they might be 3, 4, or more than that</p>
<p> </p>
<p>Sheet 1</p>
<table style="border-collapse: collapse; width: 100%; height: 54px;" border="1">
<tbody>
<tr style="height: 18px;">
<td style="width: 25%; height: 18px;">ID</td>
<td style="width: 25%; height: 18px;">Date Started</td>
<td style="width: 25%; height: 18px;">Date Finish</td>
</tr>
<tr style="height: 18px;">
<td style="width: 25%; height: 18px;">45</td>
<td style="width: 25%; height: 18px;">21/11/2021</td>
<td style="width: 25%; height: 18px;">23/11/2021</td>
</tr>
<tr style="height: 18px;">
<td style="width: 25%; height: 18px;">46</td>
<td style="width: 25%; height: 18px;">21/11/2021</td>
<td style="width: 25%; height: 18px;">26/11/2021</td>
</tr>
</tbody>
</table>
<p> </p>
<p>Sheet 2</p>
<table style="border-collapse: collapse; width: 100%; height: 168px;" border="1">
<tbody>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;">ID</td>
<td style="width: 16.6667%; height: 18px;">unit number</td>
<td style="width: 16.6667%; height: 18px;">material</td>
<td style="width: 16.6667%; height: 18px;">name</td>
<td style="width: 8.33335%;">Date</td>
<td style="width: 14.3783%; height: 18px;">time</td>
<td style="width: 10.6218%; height: 18px;">quantity</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;">
<p><span style="background-color: #ffff00;">45</span></p>
</td>
<td style="width: 16.6667%; height: 18px;">1124</td>
<td style="width: 16.6667%; height: 18px;">45648</td>
<td style="width: 16.6667%; height: 18px;">Apple</td>
<td style="width: 8.33335%;"><span style="background-color: #ffff00;">21/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">12:00 AM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
<tr style="height: 42px;">
<td style="width: 16.6667%; height: 42px;"><span style="background-color: #ffff00;">45</span></td>
<td style="width: 16.6667%; height: 42px;">
<p>1124</p>
</td>
<td style="width: 16.6667%; height: 42px;">45648</td>
<td style="width: 16.6667%; height: 42px;">Apple</td>
<td style="width: 8.33335%;"><span style="background-color: #ffff00;">22/11/2021</span></td>
<td style="width: 14.3783%; height: 42px;">1:00 AM</td>
<td style="width: 10.6218%; height: 42px;">70</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;"><span style="background-color: #ffff00;">45</span></td>
<td style="width: 16.6667%; height: 18px;">1124</td>
<td style="width: 16.6667%; height: 18px;">45648</td>
<td style="width: 16.6667%; height: 18px;">Apple</td>
<td style="width: 8.33335%;"><span style="background-color: #ffff00;">23/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">2:00 AM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;"><span style="background-color: #00ffff;">46</span></td>
<td style="width: 16.6667%; height: 18px;">1521</td>
<td style="width: 16.6667%; height: 18px;">46565</td>
<td style="width: 16.6667%; height: 18px;">Banana</td>
<td style="width: 8.33335%;"><span style="background-color: #00ffff;">21/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">5:00 PM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;"><span style="background-color: #00ffff;">46</span></td>
<td style="width: 16.6667%; height: 18px;">1521</td>
<td style="width: 16.6667%; height: 18px;">46565</td>
<td style="width: 16.6667%; height: 18px;">Banana</td>
<td style="width: 8.33335%;"><span style="background-color: #00ffff;">21/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">5:30 PM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;"><span style="background-color: #00ffff;">46</span></td>
<td style="width: 16.6667%; height: 18px;">1521</td>
<td style="width: 16.6667%; height: 18px;">46565</td>
<td style="width: 16.6667%; height: 18px;">Banana</td>
<td style="width: 8.33335%;"><span style="background-color: #00ffff;">22/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">8:00 PM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
<tr style="height: 18px;">
<td style="width: 16.6667%; height: 18px;"><span style="background-color: #00ffff;">46</span></td>
<td style="width: 16.6667%; height: 18px;">1521</td>
<td style="width: 16.6667%; height: 18px;">46565</td>
<td style="width: 16.6667%; height: 18px;">Banana</td>
<td style="width: 8.33335%;"><span style="background-color: #00ffff;">26/11/2021</span></td>
<td style="width: 14.3783%; height: 18px;">11:00 PM</td>
<td style="width: 10.6218%; height: 18px;">70</td>
</tr>
</tbody>
</table>
<p> </p>Taking into consideration that ID rows can't be determined hence they might be 3, 4, or more than that
CodePudding user response：
These formulas will only work for o365. Just tell, if you need formulas for earlier versions.
For a unique list of IDs in column A you can use
=UNIQUE(A5:A10)
In column B you can use this
=MINIFS($E$5:$E$10,$A$5:$A$10,A1)
for the oldest date. And if you switch the MINIFS to MAXIFS, you get the newest date in column C.
CodePudding user response：
As per my below screenshot, use below formulas.
A2 cell formula =IFERROR(INDEX($F$5:$F$11,MATCH(0,INDEX(COUNTIF($A$1:A1,$F$5:$F$11),0,0),0)),"")
B2 cell formula =MIN(IF($F$5:$F$11=A2,$J$5:$J$11,""))
C2 cell formula =MAX(IF($F$5:$F$11=A2,$J$5:$J$11,""))
Confirm with array entry (CTRL SHIFT ENTER) for B2 and C2 cell formulas.
If you are on Microsoft-365 then can be simplified like below.
A2=UNIQUE(F5:F11)
B2=MINIFS(J5:J11,F5:F11,A2#)
C2=MAXIFS(J5:J11,F5:F11,A2#)
