方法 2:使用 WPS 宏(VBA/JSA)
如果您需要频繁执行此操作,可以使用宏来自动化处理:
Sub MergeCaseNumbersToH()
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' 获取当前活动工作表
Set targetSheet = ActiveSheet
lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
' 遍历数据,将相同A-F列内容的G列值合并到字典中
For Row = 2 To lastRow
' 使用A-F列的组合作为字典的键
Dim key As String
key = targetSheet.Cells(Row, 1).Value & "|" & _
targetSheet.Cells(Row, 2).Value & "|" & _
targetSheet.Cells(Row, 3).Value & "|" & _
targetSheet.Cells(Row, 4).Value & "|" & _
targetSheet.Cells(Row, 5).Value & "|" & _
targetSheet.Cells(Row, 6).Value
' 获取G列的值
Dim caseNumber As String
caseNumber = targetSheet.Cells(Row, 7).Value
' 如果字典中已存在该键,追加G列的值;否则,添加新键值对
If dict.Exists(key) Then
dict(key) = dict(key) & ";" & caseNumber
Else
dict.Add key, caseNumber
End If
Next Row
' 将合并后的G列值填充到H列
For Row = 2 To lastRow
' 使用A-F列的组合作为字典的键
key = targetSheet.Cells(Row, 1).Value & "|" & _
targetSheet.Cells(Row, 2).Value & "|" & _
targetSheet.Cells(Row, 3).Value & "|" & _
targetSheet.Cells(Row, 4).Value & "|" & _
targetSheet.Cells(Row, 5).Value & "|" & _
targetSheet.Cells(Row, 6).Value
' 将字典中对应的值填充到H列
targetSheet.Cells(Row, 8).Value = dict(key)
Next Row
End Sub
G列为病案号,合并到H列,不能有错误的字符#N/A
。