ungroup column A and keep association to column B

i have the attached file where column A has arrays like this separated by a semi colon.

https://www.barringtonortho.com/; https://www.mcpc.com/; https://www.nmortho.com/; https://www.orthocenter.com/; https://www.spinaldoc.com/; https://www.stmarysregional.com/; https://triangleortho.com/; http://www.trinitymedical.com.sg/

my goal is to create a new row with only one value in col A, but also associated it with the value in col B that was tied to the array

Microsoft ExcelMicrosoft Office

Last Comment

finnstone

8/22/2022 - Mon

finnstone

12/28/2018

ASKER

here is the file
Book12.xlsx

Norie

12/28/2018

So you want each URL in it's own row and for column B of the 'new' rows to have the same value as what's currently in the same row in column B.

What about column C?

finnstone

12/28/2018

ASKER

yes/ignore C

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

Norie

12/28/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

finnstone

12/28/2018

ASKER

EXCELLENT AS USUAL

finnstone

1/2/2019

ASKER

I am getting a type mismatch. can you take a look at this file?
all-PE-fund-words.csv

Norie

1/3/2019

In row 3551 you have an error, #NAME?, value in column C.

The value in that cell is '=-nous' and Excel is treating it as a formula.

Previously you said to ignore column C, is that correct?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Norie

1/3/2019

Try this code.

Option Explicit

Sub SplitURLS()
Dim wsDst As Worksheet
Dim arrIn As Variant
Dim arrOut() As String
Dim arrURLS As Variant
Dim cnt As Long
Dim I As Long
Dim J As Long

    arrIn = Range("A1").CurrentRegion
    
    Set wsDst = Sheets.Add
    
    For I = LBound(arrIn) + 1 To UBound(arrIn)
        
        arrURLS = Split(arrIn(I, 1), ";")
        
        ReDim arrOut(1 To 2, 1 To UBound(arrURLS) + 1)
        
        cnt = 1
        
        For J = LBound(arrURLS) To UBound(arrURLS)

            arrOut(1, cnt) = Trim(arrURLS(J))
            arrOut(2, cnt) = arrIn(I, 2)
            cnt = cnt + 1
        Next J
        
        wsDst.Range("A" & Rows.Count).End(xlUp).Resize(UBound(arrOut, 2), 2).Offset(1).Value = Application.Transpose(arrOut)
    Next I
                      
End Sub