Return nth smallest non-duplicate value if there is a match in another column

[ssswww]

I have two columns of data:

Column A has a list of ID's
Column B has a list of dates

e.g.

A B
1 04/06/2015
1 21/09/2015
2 31/12/2015
2 04/04/2015
2 01/07/2015
2 04/04/2015
2 22/07/2015
3 01/01/2016
3 11/08/2015
etc

In column J I have a list of the ID's that I am ranking and then columns K-N represent ranks 1-4. I want to return the four smallest dates for any given value in column A without any duplicates. i.e. the answer should be:

J K L M N
# 1 2 3 4
1: 04/06/2015 21/09/2015 N/A N/A
2: 04/04/2015 01/07/2015 22/07/2015 31/12/2015
3: 11/08/2015 01/01/2016 N/A N/A
etc

I'm currently using the following in cell K3 and then dragged:

=(SMALL(IF($A$3:$A$999=$J3,$B$3:$B$999),K$2))

That works but displays duplicate values and so for ID 2 it gives:

04/04/2015 04/04/2015 01/07/2015 22/07/2015

Help please!

 

[DRSteele]

I think I can help. Watch this video on youtube and try to incorporate the principles into your project. Come back here and we'll solve any outstanding issues. https://www.youtube.com/watch?v=rKDI-kdBsjY

 

[sergioMabres]

Hi SW,
You could try to find a function to remove duplicates from the range after you have filtered it by id, and before you send it to SMALL, I tried myself and did not find any "Remove Duplicates" functions so I wrote one, I know is kind of slow but it does the job, to work in your case change your formula to:

=(SMALL(REMDUP(IF($A$3:$A$999=$J3,$B$3:$B$999)),K$2))

Here is the Function

Function remdup(r As Range) As Range
   Dim res, c As Range
   Dim marca, k, i, j As Integer
   k = 0
   For i = 1 To r.Rows.Count
      marca = 1
      Set c = r.Cells(i)
      For j = 1 To i - 1
         If r.Cells(j) = c Then
            marca = 0
         End If
      Next j
      If marca = 1 Then
        k = k + 1
        If k = 1 Then
           Set res = c
        Else
           Set res = Union(res, c)
        End If
      End If
   Next i
   Set remdup = res
End Function

Cheers
Sergio