52學員分7組,多餘學員排至最後一組,原始如A表,001001為第一組第一位,其組員則為001002到001007依此類推,A表組別空格希望能自動填入且不重複能生成至B表,C表只是提供參考每組的分配,主要能產出至B表
希望各位大大幫忙,感謝
Dim arrCode(52)
Sub Main()
startRow = 3
startCol = 3
'--------------------------------
nIdx = 0
For nI = 1 To 7
If nI = 7 Then nLoop = 10 Else nLoop = 7
For nJ = 1 To nLoop
nIdx = nIdx + 1
arrCode(nIdx) = Format(nI, "00#") & Format(nJ, "00#")
Next nJ
Next nI
'--------------------------------
For nI = startRow To startRow + 52 - 1
scode = Cells(nI, startCol)
If (scode <> "") Then
Call useCode(scode)
End If
Next nI
'--------------------------------
For nI = startRow To startRow + 52 - 1
scode = Cells(nI, startCol)
If (scode = "") Then
Cells(nI, startCol) = getCode
End If
Next nI
End Sub
Function useCode(ByVal code As String)
For nI = 1 To 52
If (arrCode(nI) = code) Then
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
Function getCode() As String
For nI = 1 To 52
If (arrCode(nI) <> "") Then
getCode = "'" & arrCode(nI)
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
F2:=A2、F3:=A3,依此類推。
G2:=B2、G3:=B3,依此類推。
H2:
==IF(C2<>"",C2,IF(D2="","",D2))
依此類推。
J2:=A2、J3:=A3,依此類推。
K2:=B2、K3:=B3,依此類推。
L2:
=IF(H2<>"",H2,SUMPRODUCT(SMALL(($S$2:$S$53=0)*($R$2:$R$53),SUMPRODUCT((H$2:H2="")*1)+COUNT($H$2:$H$53))))
以此類推
N2:=VLOOKUP(R2,IF({1,0},$L$2:$L$53,$J$2:$J$53),2,0)
以此類推
O2:=VLOOKUP(R2,IF({1,0},$L$2:$L$53,$K$2:$K$53),2,0)
以此類推
P2:=R2、P3:=R3,依此類推。
R2:R53輸入所有的項目(1001~7010)
S2:S53統計每個項目的個數。
S2:=COUNTIF($H$2:$H$53,R2)