Consulting

Results 1 to 4 of 4

Thread: Numbering Duplicates Consecutively

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location

    Numbering Duplicates Consecutively

    I have a table with user IDs and dates. It is sorted by user IDs first, then by dates.
    Some dates are not unique, so I have to number the duplicates consecutively.

    There are variable ranges for each user ID and i fiddled about a long time to get this code running:

    For j = 2 To last
    x = x + Worksheets("Vorlage").Range("W" & j).Value
    For i = y To x + 1
    
    Range("U" & i).FormulaR1C1 = "=COUNTIF(R" & y & "C3:R" & x + 1 & "C3,R" & i & "C3)"
    
    Next i
    y = x + 2
    Next j
    But it just count the duplicates and do not number them.

    The Address Property with RC notation is not my favorite, but Range("A1") or Range.Cells didn't work for me with countif.

    Background: The data is from a fingerprint scanner and we have to calculate the working time.
    The final table has 6 columns per day for logins and logouts (=12), because it is possible to make up to 5 breaks. It would be easy to copy the data to the correct column if i had these numbers.

    I would be grateful for any idea

    Best regards
    Susanne

  2. #2
    An attachment would have made all the difference.
    With the values to be counted in Column C and Column G free to use
    Sub Alles_Gute()
        With Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Offset(, 4)
            .Formula = "=RC[-4]&"" ""&COUNTIF(R2C[-4]:RC[-4],RC[-4])"
            .Value = .Value
        End With
    End Sub
    Change references as required.

  3. #3
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location
    Wow! That works! Thanks a lot!
    Best regards and
    Alles Gute

  4. #4
    Good to heat that it all worked to your wishes.
    Good Luck
    tschüß

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •