data source1： BH coordination table

data source2：BH layer table

process 1：Collect BH List To Layer Table

Sub FindBHForAllLayers()
    Dim ws As Worksheet
    Dim wsTarget As Worksheet
    Dim resultRow As Long
    Dim col As Long, targetCol As Long
    Dim lastCol As Long
    Dim layerName As String
    
    Set ws = ThisWorkbook.Sheets("Layer summary")
    Set wsTarget = ThisWorkbook.ActiveSheet
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 清除之前的结果
    wsTarget.Range("C2:ZZ50").ClearContents
    
    ' 遍历每个可能的layer起始列（B,G,L...）
    For targetCol = 2 To lastCol Step 5
        layerName = wsTarget.Cells(1, targetCol).Value
        If Not IsEmpty(layerName) Then
            resultRow = 2 '每个layer从第2行开始填写
            
            ' 在Layer summary中搜索这个layer
            For col = 1 To lastCol Step 5
                If Not IsEmpty(ws.Cells(1, col)) Then
                    ' 检查该组中的Description列是否包含当前layer
                    Dim rng As Range
                    Set rng = ws.Range(ws.Cells(1, col + 3), ws.Cells(50, col + 3))
                    
                    If Not IsError(Application.Match(layerName, rng, 0)) Then
                        ' 写入到layer名称的右边一列
                        wsTarget.Cells(resultRow, targetCol + 1).Value = ws.Cells(1, col).Value
                        resultRow = resultRow + 1
                    End If
                End If
            Next col
        End If
    Next targetCol
End Sub