Mark first instance of cell value in column in same row next column

[mrpavlos]

Hello all.
I just wanted to say thanks again for a great forum, and also for the help I have previously received.
I am wondering if I could have some advice how to write a macro to solve the following problem.

I have been given a spreadsheet in Excel 2010 with a Table, where the dataset is about 250k rows long. In one column of this Table there is a concatenation of values, where these values may appear either just once, or multiple times. My task is to mark in the same row, but in the next column, a number 1 to indicate that this is the first instance of the appearance of this value. For all other instances the cell should be left blank. Please see simple short example below of what I am trying to achieve. I know that a countif formula will achieve the result, and I can also write a macro that will input the countif formula as well, but as I indicated there are 250k rows and growing and I am concerned the processing speed will be very slow. I believe a dictionary would achieve what I want to achieve but I am not sure how to write it. Thanks again for taking the time to look at this, and help

Concatenation Instance

AA158 1

BB158 1

i1CC 1

BB158

i1CC

BB158

i1CC

DD158 1

AA158


Many thanks, Paul

 

[Fluff]

Which column are you interested in, also is the column to the right blank?

 

[mrpavlos]

Which column are you interested in, also is the column to the right blank?

Hello Fluff

My apologies for not being clear in my question. The column with the concatenated values would be in column BK on the spreadsheet (the table also starts from column A), with the next column being blank.
Many thanks, Paul

 

[Fluff]

Ok, how about

Sub mrpavlos()
   Dim Ary As Variant, Nary As Variant
   Dim i As Long
   
   Ary = Range("BK2", Range("BK" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         If Not .exists(Ary(i, 1)) Then
            Nary(i, 1) = 1
            .Add Ary(i, 1), Nothing
         End If
      Next i
   End With
   Range("M2").Resize(i - 1).Value = Nary
End Sub

 

[mrpavlos]

Ok, how about

Sub mrpavlos()
   Dim Ary As Variant, Nary As Variant
   Dim i As Long
  
   Ary = Range("BK2", Range("BK" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         If Not .exists(Ary(i, 1)) Then
            Nary(i, 1) = 1
            .Add Ary(i, 1), Nothing
         End If
      Next i
   End With
   Range("M2").Resize(i - 1).Value = Nary
End Sub

Cheers Fluff!

That works great! I really appreciate your taking the time to help me on this one. I learnt something new.
Many thanks again, Paul

 

[Fluff]

You're welcome & thanks for the feedback.