同一檔案號有 2~4 筆合格模組,需合併為一列,模組間以「/」分隔。
假設 A 欄檔案號,B 欄模組,C 欄輸出合併結果:
{=INDEX(B:B, MATCH(A2, A:A, 0)) & "/" &
INDEX(B:B, MATCH(A2, A:A, 0)+1) & "/" &
INDEX(B:B, MATCH(A2, A:A, 0)+2)}
以上為固定 3 模組寫法。動態筆數需搭配 TEXTJOIN(Excel 2019/365)或 VBA。
=TEXTJOIN("/", TRUE, IF(A$2:A$100=D2, B$2:B$100, ""))
三鍵確認(舊版)或直接 Enter(動態陣列版)。
Function MergeModules(lookupVal, lookupCol As Range, returnCol As Range) As String
Dim dict As Object, cell As Range, key, out As String
Set dict = CreateObject("Scripting.Dictionary")
For Each cell In lookupCol
If cell.Value = lookupVal Then
dict(cell.Offset(, returnCol.Column - lookupCol.Column).Value) = ""
End If
Next
MergeModules = Join(dict.Keys, "/")
End Function
工作表公式:=MergeModules(A2, B:B, C:C)