user13603407
user13603407

Reputation:

Find and mark first occurance of the element

Let's say I have a list of elements in A column and I want to find the first occurence of each element and mark it "YES" in B column, like this:

Column A Column B
Apple Yes
Cheese Yes
Bread Yes
Cheese No
Cheese No
Bread No
Pineapple Yes

How can I do this in VBA?

Upvotes: 2

Views: 487

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Flag First Occurrences (VBA Formula)

Excel Formula

=IF(COUNTIF(A$2:A2,A2)=1,"Yes","No")

Compact

Sub FlagFirstOccurrencesCompact()
    
    Const SourceFirstCellAddress As String = "A2"
    Const DestinationColumn As String = "B"
    Const YesFlag As String = "Yes"
    Const NoFlag As String = "No"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range(SourceFirstCellAddress)
        Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row
        If lRow < .Row Then Exit Sub
        Dim Formula As String
        Formula = "=IF(COUNTIF(" & .Address(, 0) & ":" & .Address(0, 0) & "," _
            & .Address(0, 0) & ")=1,""" & YesFlag & """,""" & NoFlag & """)"
        With .Resize(lRow - .Row + 1).EntireRow.Columns(DestinationColumn)
            .Formula = Formula
            .Value = .Value
        End With
    End With

End Sub

Argumented

Sub FlagFirstOccurrencesTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    FlagFirstOccurrences ws.Range("A2"), "B", "Yep", "Nope"
End Sub

Sub FlagFirstOccurrences( _
        ByVal SourceFirstCell As Range, _
        ByVal FlagColumn As String, _
        Optional ByVal YesFlag As String = "Yes", _
        Optional ByVal NoFlag As String = "No")
    
    With SourceFirstCell.Cells(1)
        Dim lRow As Long
        lRow = .Worksheet.Cells(.Worksheet.Rows.Count, .Column).End(xlUp).Row
        If lRow < .Row Then Exit Sub
        Dim Formula As String
        Formula = "=IF(COUNTIF(" & .Address(, 0) & ":" & .Address(0, 0) & "," _
            & .Address(0, 0) & ")=1,""" & YesFlag & """,""" & NoFlag & """)"
        With .Resize(lRow - .Row + 1).EntireRow.Columns(FlagColumn)
            .Formula = Formula
            .Value = .Value
        End With
    End With

End Sub

Upvotes: 2

Related Questions