0

We collect sets of alphanumeric values called variants through an online form, the output of which is presented in an Excel spreadsheet. The base product has a set of Base Variants. Options can be added to the base product. Each option has its own set of variants, which are collected into multiple cells with label "ADD Variants". I have added an image of a sample file with Base variant, 2 ADD Variants and 2 REMOVE Variants: Screenshot

My requirements are as follows:

  1. All values from ADD Variants cells need to appended to Base Variants list. Values from multiple REMOVE Variants are looked up and are deleted from Base Variants (Base Variants + ADD Variants - REMOVE Variants)
  2. Finally, I need a string of Base Variants, each of them delimited by a Comma and a single space, which can be copied and pasted into our supplier's ordering portal. This will bring up the product with the chosen options added in the portal.

Can someone help with how the above could be achieved? I also need to automate this process. Any help is hugely appreciated!

3
  • 1
    Welcome to Super User,, please edit your post & add some sample data,, since what you are trying to ask is not clear and loud !! Sep 9, 2020 at 5:42
  • Question title is about columns, but text sounds more like you want to add and remove values from a string. Either way, you might want to look into the Split() function. Sep 9, 2020 at 6:04
  • It should be easily doable with Power query. If you have problems setting it up, post an example of your source data (with any sensitive information replaced) and desired results. Sep 9, 2020 at 18:57

1 Answer 1

1

You can try a user defined function:

added functionality for multiple remove input range.

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

Original requirement below:

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

Insert the function into a module in the developer tab, then use it like:

=CombineMe(C2,D2,E2)

enter image description here

to give the result

enter image description here

5
  • Thank you Justin. I have made an edit to my post and also added a screenshot of my data. Could you please check it out and let me know if your solution works for this scenario? You have captured my requirement very well. The only question I have is whether it would work for 1 Base variant string + Multiple ADD Variants cells - Multiple REMOVE Variant cells. Thank you!
    – Nats NZ
    Sep 14, 2020 at 2:49
  • Have you tried it on your data? Sep 14, 2020 at 3:11
  • I think the new code should do what you are after, note that if you want to ignore upper case vs lower case place the term "Option Compare Text" at the top of the module (outside the function code at the very top of the page.) Sep 14, 2020 at 4:26
  • Hi Nats, if you are happy with the solution can you tick it off as solved, otherwise let me know what the issue is. Sep 14, 2020 at 11:20
  • Thanks Justin. I will be trying this solution in a day or two. I will certainly keep you posted.
    – Nats NZ
    Sep 14, 2020 at 21:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .