find 1st, 2nd and 3rd small dates in column for same code in other column

[zuriqi]

Hi,

I am looking for a formula to find the 1st, 2nd and 3rd smallest value for the date of the "Date" column which has the same code in "Code" column. Formula to give space if cell in "Date" Column is empty or if code is repeated in column C, D or E. as shown in the table below.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]1st Small[/TD]
[TD="align: center"]2nd Small[/TD]
[TD="align: center"]3rd Small[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]01-Oct-17[/TD]
[TD="align: center"]A[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91, align: center"]3-Jul-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: center"]1-Oct-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93, align: center"]15-Oct-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]01-Oct-17[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]4-Jan-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]B[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91, align: center"]4-Jan-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: center"]19-Aug-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]6-Aug-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]D[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91, align: center"]6-Feb-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: center"]1-Jun-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93, align: center"]6-Aug-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]5-May-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]C[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91, align: center"]5-May-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]15-Oct-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]1-Jun-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]6-Feb-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]19-Aug-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: center"]3-Jul-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Put this formula in C2:

=IFERROR(IF(COUNTIF($B$2:$B4,$B4)>1,"",SMALL(IF(($B$2:$B$13=$B4)*($A$2:$A$13<>""),IF(ISERROR(MATCH($A$2:$A$13,$B4:B4,0)),$A$2:$A$13)),1)),"")

change the ranges to match your sheet, and confirm with Control+Shift+Enter.

Then drag it down and across as needed.

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, copy across, and down:

=IF(ISNA(MATCH($B2,$B$1:$B1,0)),IFERROR(IF(MIN(IF($B$2:$B$13=$B2,IF(ISNUMBER(1/$A$2:$A$13),IF(ISNA(MATCH($A$2:$A$13,$B2:B2,0)),$A$2:$A$13))))=0,"",MIN(IF($B$2:$B$13=$B2,IF(ISNUMBER(1/$A$2:$A$13),IF(ISNA(MATCH($A$2:$A$13,$B2:B2,0)),$A$2:$A$13))))),""),"")

If you would want to custom format the formula cell as follows:

[=0]"";d-mmm-yy

you can in C2 control+shift+enter, not just enter, copy across, and down:

=IF(ISNA(MATCH($B2,$B$1:$B1,0)),IFERROR(MIN(IF($B$2:$B$13=$B2,IF(ISNUMBER(1/$A$2:$A$13),IF(ISNA(MATCH($A$2:$A$13,$B2:B2,0)),$A$2:$A$13,"")))),""),"")

 

[zuriqi]

Great Aladin, it works like charm , what about the 2nd small and 3rd ? i mean formula in D2 and E2?

 

[Aladin Akyurek]

Great Aladin, it works like charm , what about the 2nd small and 3rd ? i mean formula in D2 and E2?

You need to copy C2 across to E2 and down.

 

[zuriqi]

Woow thanks a lot Aladin , you really solve an issue in my database.. Thank you very much

 

[Aladin Akyurek]

Woow thanks a lot Aladin , you really solve an issue in my database.. Thank you very much

You are welcome.