Excelの質問です。

とりあえず、VBA での実装で例です。

左のデータがあるシートのシートタブを右クリックし、「コードの表示」で下記コードを貼り付け、

実行してみてください。

Sub makeTable()
    Dim dstWS As Worksheet
    
    Set dstWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    
    Dim wd As Long
    Dim lastLine As Long
    Dim i As Long
'--- とりあえず、曜日数だけ処理
    For wd = 1 To 7
        If Cells(2, wd + 1) = "" Then Exit For
        dstWS.Cells(3 * wd - 1, 1).Value = wd
        
'--- 各曜日の最終行まで処理
        lastLine = Cells(65535, wd).End(xlUp).Row
        For i = 3 To lastLine
            If Cells(i, wd + 1).Value <> "" Then
                addList dstWS, wd, Cells(i, wd + 1).Value, Cells(i, "A").Value
            End If
        Next
    Next

'--- データ範囲に罫線を記入
    Dim lastRow As Long
    lastRow = dstWS.Range("IV1").End(xlToLeft).Column
    For i = wd - 1 To 1 Step -1
        With dstWS.Cells(3 * i - 1, 1)
            .Value = i
            .Resize(1, lastRow).Borders(xlEdgeTop).LineStyle = xlContinuous
            .Resize(1, lastRow).Borders(xlEdgeTop).Weight = xlMedium
        End With
    Next
End Sub

Sub addList(dstWS As Worksheet, wd As Long, title As String, member As String)
    Dim tt As Long
    tt = 2
'--- データ列を検索
    Do While dstWS.Cells(1, tt) <> title
        tt = tt + 1
        If dstWS.Cells(1, tt) = "" Then
           dstWS.Cells(1, tt) = title
            Exit Do
        End If
    Loop
    
'--- データ行を検索
    For dn = 3 * wd - 1 To 3 * wd + 1
        If dstWS.Cells(dn, tt) = "" Then
            dstWS.Cells(dn, tt) = member
            Exit Sub
        End If
    Next
'--- 4人目はエラー
    MsgBox title & "の" & wd & "はすでに３人います。" & vbNewLine & member & "は登録されません。"
End Sub

http://www2s.biglobe.ne.jp/~iryo/kabu/tool/vba/kabuvba/kabuvba1....