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

#### ssswww

##### New Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
Code:
``````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

Last edited:

Replies
2
Views
153
Replies
4
Views
533
Replies
3
Views
113
Replies
3
Views
495
Replies
1
Views
401

1,221,498
Messages
6,160,161
Members
451,627
Latest member
WORBY10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back