CODEWITHSUNDEEP
excel-formulanumbers
mwilliams
mwilliams

Reputation: 5

Excel formula to assign serial number to duplicates with date criteria

I need excel formula to assign serial number to duplicates but with an added criteria for dates in ascending order.

The Count if formula "=COUNTIF($A$1:A1,A1)" will assign number in ascending order based on the row for the column A data but not based on the date.

I have the below table in Column A and B and the result expected in Column C

Name         Date       Serial number(Expected Result)
Apple        1/1/2019   1
Orange       2/1/2019   1
Lemon        4/1/2019   2
Banana       5/1/2019   2
Apple        8/1/2019   3
Watermelon   1/1/2019   1
Lemon        1/1/2019   1
Orange       4/1/2019   2
Banana       2/1/2019   1
Apple        5/1/2019   2

If you see first Apple is 1 but next one is not 2 as the date is 8th so its the third date based on ascending order of the date hence its 3 and the last Apple is 2. Hope I am clear what I am asking. Apologies if any inconvenience in writing as this is my first question here and I am still learning.

Upvotes: 0

Views: 2078

Answers (1)

Skin
Skin

Reputation: 11217

If you sort the data, the formula you have will work.

If you're not able to/willing to sort the data then you can use this custom formula ...

Public Function AssignSerialNo(ByVal strName As String, ByVal dtDate As Date, ByVal rngData As Range) As Long
    Dim lngRow As Long, lngEmptyCount As Long, strThisName As String, dtThisDate As Date, strThisDate As String

    Application.Volatile

    AssignSerialNo = 1

    ' Process each row provided in the 3rd parameter.  Give it 10 rows of blanks before exiting out
    ' and ending the process.
    For lngRow = 1 To rngData.Rows.Count
        strThisName = Trim(rngData.Cells(lngRow, 1))
        strThisDate = rngData.Cells(lngRow, 2)

        If strThisName = "" Then
            lngEmptyCount = lngEmptyCount + 1
        Else
            lngEmptyCount = 0

            If IsDate(strThisDate) Then
                dtThisDate = rngData.Cells(lngRow, 2)

                If UCase(strThisName) = UCase(strName) Then
                    ' We have a match, determine if the date value is greater than or less than the parameter.
                    If dtThisDate < dtDate Then AssignSerialNo = AssignSerialNo + 1
                End If
            End If
        End If

        If lngEmptyCount >= 10 Then Exit For
    Next
End Function

Add the following formula in the first "Serial Number" cell and fill down ...

=AssignSerialNo(A2,B2,A:B)

... this is all assuming that your example table (with headings) starts from cell A1.

Let me know how you go.

Upvotes: 1

Related Questions