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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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:

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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