Splitting multiple lines in a cell into multiple rows with reference cell

[Galaxorian]

Hey there,

I have a question (see title). I have this:

And want to accomplish this:

This is for like 11000 companies. Every cell in column B holds the amount of companies in an alliance (2 or more) but the cross equity transfger is specified only once. How do I make it that every row shows the company name AND the correct cross equity transfer.

Thanks in advance kind strangers!

 

[Trixterz]

If you want to do this through macro then see the code below...

Sub SplitNArrange()
    Dim CellValue As Variant, CounterRows As Integer, CounterLines As Integer
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    For CounterRows = Split(ActiveSheet.UsedRange.Address, "$")(4) To 1 Step -1
        CellValue = ActiveSheet.Range("B" & CounterRows).Value
        
        If CellValue Like "*" & vbLf & "*" Then
            CellValue = Split(CellValue, vbLf)
            
            For CounterLines = LBound(CellValue) To UBound(CellValue)
                ActiveSheet.Rows(CounterRows + CounterLines + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                ActiveSheet.Range("A" & CounterRows + CounterLines + 1).Value = CellValue(CounterLines)
                ActiveSheet.Range("B" & CounterRows + CounterLines + 1).Value = ActiveSheet.Range("C" & CounterRows).Value
            Next CounterLines
            
            ActiveSheet.Rows(CounterRows).Delete
        End If
    Next CounterRows
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Beep
    MsgBox "Completed"
End Sub

P.S. Do not use this on your original document without testing it first.