Formula Help: If value in column A has already occurred in list then add +1 to column B

[ianhagen]

Looking for an Alteryx solution to something that I created in Excel VBA. The VBA version takes about 12 minutes to run with 25k rows of data. Each month adds 8k more rows so by February 2023 the macro will be done running for December 2022 data. 

Anyways this is essentially the task. I get ~8k rows of data each month. The file includes the previous months' data. In March, the file included January, February, and March data. I need to remove the ~16k rows of January/February data. There is no primary key or key of any kind. I created a key with a concatenation and a number that is based on the number of occurrences of the concatenated line in the file from the previous month.

Example: the "Occurrence" field is the one I need Alteryx to create

Color               Occurrence

Blue                          1

Blue                          2

Blue                          3

Red                          1

Green                       1

Green                       2

Purple                      1

Red                          2

Red                          3

Purple                      2  

Blue                         4



If it helps the VBA that I initially wrote is this:

For Each r In rnData
    Set lastrow2 = .Columns("Z").Find(what:=r.Value)
    Set lastrow2 = .Columns("Z").FindPrevious(after:=r)
    If lastrow2.Row >= r.Row Then
        .Cells(r.Row, 27) = 1
        Else
            .Cells(r.Row, 27) = .Cells(lastrow2.Row, 27).Value + 1
        End If

Next

I appreciate any help, ideas, or recommendations! 

[Luke_C · Accepted solution]

Hi @ianhagen 

Something like this should work. I used the record ID tool to keep the order, and the tile tool to assign the occurrence based on the color.

[ianhagen]

@Luke_C  thanks for the fast reply! Definitely didn't think it was going to be this easy. Thank you for the help!