从多个单元格中附加和删除值，将结果单元格转换为列

Question

我们通过在线表单收集一组称为变体的字母数字值，其输出显示在 Excel 电子表格中。基础产品有一组基础变体。可以将选项添加到基础产品中。每个选项都有自己的一组变体，这些变体被收集到带有标签“添加变体”的多个单元格中。我添加了一个示例文件的图像，其中包含基础变体、2 个添加变体和 2 个删除变体：截屏

我的要求如下：

1. 所有来自“添加变体”单元格的值都需要附加到“基本变体”列表中。查找来自多个“删除变体”的值并将其从“基本变体”中删除（基本变体 + 添加变体 - 删除变体）
2. 最后，我需要一个基本变体字符串，每个变体都由逗号和一个空格分隔，可以将其复制并粘贴到我们供应商的订购门户中。这将在门户中显示已添加所选选项的产品。

有人能帮我实现上述目标吗？我也需要自动化这个过程。任何帮助我都非常感谢！

Answer 1

您可以尝试用户定义的函数：

增加了多个删除输入范围的功能。

Function CombineMe(BaseVar As Range, AddVar As Range, RemVar As Range) As String
Dim MyBVar As Variant, MyAddVar As Variant, MyRemVar As Variant
Dim i As Long, j As Long
Dim MyStr As String, MyAddStr As String
Dim c As Range


For Each c In RemVar
    If MyStr = vbNullString Then
        MyBVar = Split(BaseVar, ",")
    Else
        MyBVar = Split(MyStr, ",")
        MyStr = vbNullString
    End If
    MyRemVar = Split(c, ",")
    For i = LBound(MyRemVar) To UBound(MyRemVar)
        For j = LBound(MyBVar) To UBound(MyBVar)
            If MyBVar(j) = MyRemVar(i) Then MyBVar(j) = vbNullString
        Next j
    Next i
    For i = LBound(MyBVar) To UBound(MyBVar)
        If MyBVar(i) = vbNullString Then GoTo MyNxti
        If MyStr = vbNullString Then
            MyStr = CStr(MyBVar(i))
        Else
            MyStr = MyStr & "," & CStr(MyBVar(i))
        End If
MyNxti:
    Next i
Next c
    MyStr = MyStr & "," & CStr(AddVar)
    CombineMe = MyStr
End Function

原始要求如下：

Function CombineMe(BaseVar As Range, AddVar As Range, RemVar As Range) As String
Dim MyBVar As Variant, MyAddVar As Variant, MyRemVar As Variant
Dim i As Long, j As Long
Dim MyStr As String
MyBVar = Split(BaseVar, ",")
MyRemVar = Split(RemVar, ",")
For i = LBound(MyRemVar) To UBound(MyRemVar)
    For j = LBound(MyBVar) To UBound(MyBVar)
        If MyBVar(j) = MyRemVar(i) Then MyBVar(j) = vbNullString
    Next j
Next i
For i = LBound(MyBVar) To UBound(MyBVar)
    If MyBVar(i) = vbNullString Then GoTo MyNxti
    If MyStr = vbNullString Then
        MyStr = CStr(MyBVar(i))
    Else
        MyStr = MyStr & "," & CStr(MyBVar(i))
    End If
MyNxti:
Next i
MyStr = MyStr & "," & CStr(AddVar)
CombineMe = MyStr
End Function

将该函数插入到开发人员选项卡中的模块中，然后像这样使用它：

=CombineMe(C2,D2,E2)

给出结果