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

ssswww

New Member
Joined
Feb 18, 2016
Messages
1
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,511
Messages
5,854,149
Members
431,620
Latest member
imsyun

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?

Disable AdBlock

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
Top