Combining Columns

[timohteee]

Hi,

I've come for some (hopefully) quick macro help. I have a spreadsheet where I need to Combine columns A and C with the operator in column B to give the expected results below. For each search term in column A, it needs to be run with every search term in column B separately.

For example:

I'm looking for a new column (or sheet) with the terms combined as follows:

• For each search term in column A, it needs to be run with every search term in column B separately.

• DRL AND settlement
• DRL AND agreement
• DRL AND consent
• DRL AND "future patents"
• DRL AND application OR applications
• DRL AND license
• DRL AND covenant
• Jim AND settlement
• Jim AND agreement
• Jim AND consent

etc, etc.
​

I believe a for loop is what I am looking for.

Any help is greatly appreciated.

 

[mumps]

Try:

Sub CombineCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim modifier As Range
    Dim anchor As Range
    For Each anchor In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        'set rng=
        For Each modifier In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = anchor & " " & anchor.Offset(0, 1).MergeArea.Cells(1, 1).Value & " " & modifier
        Next modifier
    Next anchor
    Application.ScreenUpdating = True
End Sub

Result will be in column D.

 

[timohteee]

Try:

Sub CombineCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim modifier As Range
    Dim anchor As Range
    For Each anchor In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        'set rng=
        For Each modifier In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = anchor & " " & anchor.Offset(0, 1).MergeArea.Cells(1, 1).Value & " " & modifier
        Next modifier
    Next anchor
    Application.ScreenUpdating = True
End Sub

Result will be in column D.

This works perfectly!

Thank you so much!

 

[mumps]

You are very welcome.

 

[Rick Rothstein]

Here is another macro that you can consider (it uses only one loop through the "Anchors")...

[table="width: 500"]
[tr]
	[td]Sub CombineColumns()
  Dim Combo As String, Combined As String, Anchor As Variant, Parts() As String
  Combo = "|@ " & Range("B2") & " " & Join(Application.Transpose(Range("C2", Cells(Rows.Count, "C").End(xlUp)).Value), "|@ " & Range("B2").Value & " ")
  For Each Anchor In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Combined = Combined & Replace(Combo, "@", Anchor)
  Next
  Parts = Split(Mid(Combined, 2), "|")
  Range("E1").Resize(UBound(Parts) + 1) = Application.Transpose(Parts)
End Sub[/td]
[/tr]
[/table]

 

[timohteee]

Thanks for your macro, Rick.

I will give it a go tomorrow as well.