[Solved] excel multiple column matches [closed]

by


This code will do what you are intending with the information given. I encourage you to look up the methods used as they are quite fundamental and novice in difficulty. I have put down comments that explain what each section is doing. GL 

Sub test()
Dim filter, C1, C2 As String
Dim counter As Integer
Dim letters() As String
Dim i, x As Integer
Dim char As String

Cells(1, 3).Select
x = 1

'Checks to see if the third column is blank, if not it will continue
While Cells(x, 3).Value <> ""

'Re instantiates an array of size 3 to store the letters (technically 4 since 0 is also a position) Also erases past stored data
ReDim letters(3) As String
i = 0
'Filter stores the whole string
filter = ActiveCell.Value

'Stores the column A and B values  of that row in C1 and C2
C1 = ActiveCell.Offset(, -2).Value
C2 = ActiveCell.Offset(, -1).Value

'This for loop goes through each character of filter string
For counter = 1 To Len(filter)
'char stores the character
char = Mid(filter, counter, 1)
    If char = " " Or char = "&" Then
    Else:
    'stores any character not blank and "&" in an array
    letters(i) = char
    i = i + 1
    End If
Next

'If statement to skip rows that only have 1 letter
If letters(1) <> Empty Then

'For loop that will create a new line and print out array letters in each new row
For i = 0 To 1
ActiveCell.Value = letters(i)
  ActiveCell.Offset(1).EntireRow.Insert
  ActiveCell.Offset(1).Select
  ActiveCell.Offset(, -2).Value = C1
  ActiveCell.Offset(, -1) = C2
  ActiveCell.Value = letters(i + 1)
Next i
End If
x = x + 1
ActiveCell.Offset(1).Select

Wend
End Sub

2

solved excel multiple column matches [closed]