Is there a way in Excel to read through 3 columns simultaneously and add the sequence of specific occurrences in a new column?

I have a large spreadsheet containing around 60,000 rows.

They are records of events from 98 camera traps. I have a column called "Station Number" which contains the number assigned to each camera trap (these are numbered from 1-98 and there are multiple occurrences of each number in this column as there are multiple rows/camera images from each camera trap).

I now would like to find the sequence of male and female occurrences on these camera traps.

I manually recorded these for the 50,000 images into two separate columns called "N_Male" and "N_Female". When a male appears in the images, this row in the "N_Male" column was marked as "1", and the same for when females appear.

I am now looking to find a way to obtain the sequence/order of male and female occurrences for each of the 98 stations with either an output that is either a new table/columns with all of the information or one single output for each camera like "Camera 1: MFMMFFFMFMMMF...etc".

I started doing this manually in two sheets but realized it will take me a considerable amount of time (there are over 50,000 rows). Is there any way to automate this using VBA or macro or formulas etc in Excel? Please see the example below of the first rows of the spreadsheet:

Example of the output from the above:

Camera 1: MFM
Camera 2: FF
Camera 3: M
Camera 4: MMF

I tried first doing this manually (very time consuming). But then tried using COUNTIFS formulas. It was successfully reading through the first columns and understanding the different occurrences in the male and female columns, but was recording this instead numerically. I then considered using CONCAT and tried it but as the input is numerical (e.g "N_Male = 1), it wasn't understanding it fully. Perhaps some substitute concat function could work? or textsplit?

I am really a newbie to these functions in Excel so any advice or guidance would be hugely appreciated.

Another version using formula:

=LET(a,A2:A13,
     b,B2:B13,
     c,C2:C13,
     d,IFERROR(IFS(b,"M",c,"F"),""),
     u,UNIQUE(a),
     m,MAP(u,LAMBDA(y,CONCAT(FILTER(d,a=y)))),
HSTACK(u,m))

With Formula:

=LET(
    rng,A:C,
    flt,DROP(FILTER(rng,TAKE(rng,,1)<>""),1),
    a,TAKE(flt,,1),
    bc,TAKE(flt,,-2),
    unq,UNIQUE(a),
    cnc,BYROW(unq,
        LAMBDA(r,CONCAT(BYROW(FILTER(bc,a=r),
            LAMBDA(x,IF(TAKE(x,,1)=1,"M",IF(TAKE(x,,-1)=1,"F",""))))))),
    HSTACK(unq,cnc))

Join Matches

=LET(data,A2:C13,nCol,1,mCol,2,fCol,3,TwoCols,FALSE,mfVal,1,
        lDel,"Camera ",mDel,": ",dvDel,"",dmVal,"M",dfVal,"F",dnVal,"",
    su,INDEX(data,,nCol),sm,INDEX(data,,mCol),sf,INDEX(data,,fCol),
    du,UNIQUE(su),
    df,IF(sm+sf=mfVal,IF(sm=mfVal,dmVal,dfVal),dnVal),
    dv,BYROW(du,LAMBDA(r,TEXTJOIN(dvDel,1,FILTER(df,su=r)))),
    dr,IF(TwoCols,HSTACK(lDel&du,dv),lDel&du&mDel&dv),
dr)

The following routine create a dictionary with the camera number as key. It first reads all the data into an array to speed up the process, then loops over the data row by row and creates a string (in variable sequence) for every row.

As I always advocate for early binding: Add a reference to the Microsoft Scripting.Library in your VBA project so that you can use the Dictionary class.

Function createCameraDict(r As Range) As Scripting.Dictionary
    Dim cameraDict As New Dictionary

    Dim data As Variant, row As Long
    data = r.Value   ' Creates a 2-dimensional array from the data 
    
    Dim camera As String, sequence As String
    For row = 2 To UBound(data, 1)
        camera = data(row, 1)
        ' Create a string like "MF" from cells 2 and 3 of that row.
        sequence = String(data(row, 2), "M") & String(data(row, 3), "F")
        If sequence <> "" Then
            ' Add the sequence to the already existing sequence for that camera.
            ' (camera will be added automatically if missing in the Dict)
            cameraDict(camera) = cameraDict(camera) & sequence
        End If
    Next row
    Set createCameraDict = cameraDict
End Function

Now a test could look like

Sub test()
    Dim d As Scripting.Dictionary
    Set d = createCameraDict(ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion)
    
    Dim camera
    For Each camera In d.Keys
        ' (Here you could write the data into a sheet or whatever you want.)
        Debug.Print "Camera " & camera & ": " & d(camera)
    Next
End Sub

A two-dimensional array (index + string) for the 98 entries is created, then it is written in I2.

Sub createSequence()

Dim xLAppL As Excel.Application: Set xLAppL = GetObject(, "Excel.Application")
Dim wrkBk As Excel.Workbook: Set wrkBk = xLAppL.ActiveWorkbook
Dim wrksH As Worksheet: Set wrksH = wrkBk.ActiveSheet
Dim wrksHTrgt As Worksheet: Set wrksHTrgt = wrkBk.Sheets("Sheet8")

Dim rngStationArr() As Variant: rngStationArr = wrksH.Range("a1").CurrentRegion

Dim i As Long
Dim stationCount As Long

Dim stationStr() As String: ReDim stationStr(1 To 98, 1 To 2) 'edit here the number of groups


    For i = 2 To UBound(rngStationArr, 1)
        stationCount = rngStationArr(i, 1)
        Dim stationData As String: stationData = stationStr(stationCount, 2)
        stationStr(stationCount, 1) = stationCount
        
            If rngStationArr(i, 2) = 1 Then
                stationStr(stationCount, 2) = stationData & "M"
            ElseIf rngStationArr(i, 3) = 1 Then
                stationStr(stationCount, 2) = stationData & "F"
            End If
    Next i
    
    wrksHTrgt.Range("I2").Resize(UBound(stationStr, 1), UBound(stationStr, 2)).Value2 = stationStr

End Sub