Excel Min Date in a given range

Question

I have an Excel file with multiple columns. If you observe the attached image the 1st column is coupons (repeated with different settlement dates). I need to write a macro which will loop through the file, and find one record for each coupon with the minimum date of all the dates that particular coupon has. For example, coupon 2 has 4 records in the attached image. I should delete three off them, and have only one record with the earliest date among those four.

Can someone please provide me an example?

Answer 1

One possibly is to use a temporary array formula. Assuming field Coupon is column B and Date is column C then in the next free column, say column N use {=IF(C2=MIN(IF($B:$B=B2,$C:$C)),TRUE,FALSE)}

Then use an advanced filter to filter on Coupon and TRUE in Column N. In this example I've set up the criteria and output from column Q

Eg VBA Code example

Sub test()
Dim rng As Range, strR1c1 As String

'identify minimum date using array formula
With Sheet1

    .Range("N1").Value = "Temp Header"
    'array formula = {=IF(C2=MIN(IF($B:$B=B2,$C:$C)),TRUE,FALSE)}
    .Range("N2").FormulaArray = "=IF(RC[-11]=MIN(IF(C2=RC[-12],C3)),TRUE,FALSE)"
    strR1c1 = .Range("N2").FormulaR1C1
    Set rng = .Range("N2:N" & .Range("B" & .Rows.Count).End(xlUp).Row)

    rng.Formula = strR1c1
    rng.FormulaArray = rng.FormulaR1C1

    'Advanced Filter criteria requirements to new range
    .Range("B1:N11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
        "Q1:AC2"), CopyToRange:=.Range("Q5:AC5"), Unique:=False

    'tidy up - clear array formula
    .Range("N:N").ClearContents

End With


End Sub

Answer 2

This link is helpful for you. This also include a sample example.