Merging Multiple Cells Into One Cell Based on Parameters

[JazzL]

Hi guys,

I've crawled a website and each product has numerous URL versions (due to different URL structures). See examples below:

www.test.com.au/firepit-30cm-black
www.test.com.au/heating/firepit-30cm-black
www.test.com.au/heating/outdoor-heating/firepit-30cm-black
www.test.com.au/fire-pits/firepit-30cm-black
www.test.com.au/heating/fire-pits/firepit-30cm-black

Each of these URLs are in separate cells at the moment, but I want them to be merged into one cell with line breaks.

Is there a formula to merge these into the one cell with line breaks, based on the parameter "/firepit-30cm-black"?

We've got to roll this out for all products across the site so we need a formula rather than taking a manual approach.

There also isn't a set number of URL versions, so some products might have 3 URLs and other product might have 6 URLs.

The only consistent parameter in the URL structure is the product name (eg. "firepit-30cm-black").

Any help would be appreciated!

Cheers,

Jazz

 

[hiker95]

JazzL,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider.

You can change the raw data worksheet name in the macro.

In the raw data worksheet in cell D1, going down, add the search strings.

Sample raw data worksheet:

Excel 2007
	A	B	C	D	E
1	www.test.com.au/firepit-30cm-black			firepit-30cm-black
2	www.test.com.au/heating/firepit-30cm-black
3	http://www.test.com.au/heating/outdo...pit-30cm-black
4	www.test.com.au/fire-pits/firepit-30cm-black
5	http://www.test.com.au/heating/fire-...pit-30cm-black
6
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E
1	www.test.com.au/firepit-30cm-black			firepit-30cm-black	[url]www.test.com.au/firepit-30cm-black[/url] [url]www.test.com.au/heating/firepit-30cm-black[/url] www.test.com.au/fire-pits/firepit-30cm-black
2	www.test.com.au/heating/firepit-30cm-black
3	http://www.test.com.au/heating/outdo...pit-30cm-black
4	www.test.com.au/fire-pits/firepit-30cm-black
5	http://www.test.com.au/heating/fire-...pit-30cm-black
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub MergeMultipleURLs()
' hiker95, 12/01/2016, ME978947
Dim a As Range, d As Range, s As String, w As Double, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Columns(1).AutoFit
  w = .Columns(1).ColumnWidth
  .Columns(5).ColumnWidth = w
  For Each d In .Range("D1", Range("D" & Rows.Count).End(xlUp))
    n = 0
    For Each a In .Range("A1", Range("A" & Rows.Count).End(xlUp))
      If InStr(a, d.Value) Then
        If n = 0 Then
          d.Offset(, 1) = a
          n = n + 1
        Else
          d.Offset(, 1) = d.Offset(, 1) & vbLf & a
        End If
      End If
    Next a
  Next d
  .Columns(5).ColumnWidth = w
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the MergeMultipleURLs macro.

 

[hiker95]

JazzL,

Same macro, but, with sample raw data, and, results:

Excel 2007
	A	B	C	D	E
1	www.test.com.au/firepit-30cm-black			/firepit-30cm-black	[url]www.test.com.au/firepit-30cm-black[/url] [url]www.test.com.au/heating/firepit-30cm-black[/url] www.test.com.au/fire-pits/firepit-30cm-black
2	www.test.com.au/heating/firepit-30cm-black
3	http://www.test.com.au/heating/outdo...pit-30cm-black
4	www.test.com.au/fire-pits/firepit-30cm-black
5	http://www.test.com.au/heating/fire-...pit-30cm-black
6
Sheet1