Help with sequential counting

[asjmoron]

Hi All,

I think I may have found something that can not be done!

Basically, I have the below (this spans circa 6k rows but this is just a snippet)

kf_Client_Reference_Number	From_Date
0011	10/11/2014​
0011	28/02/2014​
0014	22/08/2016​
0014	07/12/2015​
0014	14/08/2014​
0014	06/09/2013​
0016	19/09/2016​
0016	02/09/2015​
0016	02/09/2014​
0016	17/09/2013​

I am trying to trim this data to nly keep the two most recent records based on reference number. So, the above would then be

kf_Client_Reference_Number	From_Date
0011	10/11/2014​
0011	28/02/2014​
0014	22/08/2016​
0014	07/12/2015​
0016	19/09/2016​
0016	02/09/2015​

I hope that makes sense?

Thanks in advance for any advice

 

[Fluff]

What version of excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Another thing, will the dates in col B always be sorted newest to oldest?

 

[asjmoron]

Another thing, will the dates in col B always be sorted newest to oldest?

Im using 2016 (sorry it's been a while since I've been here!).

In the interest of trying to make this as painless as possibe, yes, I have the column ordered Newest to Oldest.

 

[Peter_SSs]

If your data is sorted like that could you use this formula copied down then filter column C for "1" values and delete those rows?

21 08 23.xlsm
	A	B	C
1	kf_Client_Reference_Number	From_Date	Check
2	11	11/10/2014
3	11	28/02/2014
4	14	22/08/2016
5	14	12/07/2015
6	14	14/08/2014	1
7	14	9/06/2013	1
8	16	19/09/2016
9	16	9/02/2015
10	16	9/02/2014	1
11	16	17/09/2013	1
Keep 2

Cell Formulas
Range	Formula
C2:C11	C2	=IF(COUNTIF(A$2:A2,A2)<3,"",1)

 

[veyselemre]

Try, this.

Sub test()

    With Sheets("Sheet1")
        
        With .Range("C2:C" & .Cells(Rows.Count, 1).End(3).Row)
            .Formula = "=COUNTIF(A$2:A2,A2)"
            .Value = .Value
        End With

        If .AutoFilterMode = False Then .Rows(1).AutoFilter
        .Range("A1").CurrentRegion.AutoFilter 3, ">2"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilter.ShowAllData
        .Range("C:C").ClearContents
        .AutoFilterMode = False
    
    End With

End Sub

 

[Peter_SSs]

If you want to use a macro and there are a reasonable number of disjoint rows to be deleted out of your 6k rows then I think you would find this noticeably faster.
I have assumed the data in columns A:B, column C empty and that there will be at least one row to delete. Code can be adapted if any of that may not be the case.

Sub Keep_Two()
  With Range("C4:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[-2]=R[-2]C[-2],1,"""")"
    .Value = .Value
    .Offset(, -2).Resize(, 3).Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
    .SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
  End With
End Sub