Splitting text with line breaks with repeating adjacent data

[Scott F]

Is it possible to split line broken text from one column and repeat the data from adjacent columns as shown? The values in the data column are in a single cell per row with line breaks.

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column3", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3")
in
    #"Split Column by Delimiter"

Book2
	A	B	C	D	E	F	G
1	Column1	Column2	Column3		Column1	Column2	Column3
2	Company1	Phone1	Data1 Data2 Data3 Data4		Company1	Phone1	Data1
3	Company2	Phone2	Data1 Data2 Data3 Data4		Company1	Phone1	Data2
4					Company1	Phone1	Data3
5					Company1	Phone1	Data4
6					Company2	Phone2	Data1
7					Company2	Phone2	Data2
8					Company2	Phone2	Data3
9					Company2	Phone2	Data4
Sheet1

 

[Akuini]

Try this:

Sub Scott_1()
Dim i As Long, k As Long
Dim va, vb, ary, x
va = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 3)

ReDim vb(1 To UBound(va, 1) * 5, 1 To 3)  'I'm assuming the average number of phone in each company is no more than 5

For i = 1 To UBound(va, 1)
    ary = Split(va(i, 3), vbLf)
        For Each x In ary
            k = k + 1
            vb(k, 1) = va(i, 1)
            vb(k, 2) = va(i, 2)
            vb(k, 3) = x
        Next
Next

'put the result in sheet2
Sheets("Sheet2").Range("A2").Resize(k, 3) = vb
End Sub

Notes:

1. I'm assuming the average number of phone in each company is no more than 5.
2. I put the result in sheet2.

Example:

Book1
	A	B	C
1
2	Company 1	phone 1	1234 1235 1236
3	Company 2	phone 2	1237
4	Company 3	phone 3	1238 1239
Sheet5

Result:

Book1
	A	B	C
1
2	Company 1	phone 1	1234
3	Company 1	phone 1	1235
4	Company 1	phone 1	1236
5	Company 2	phone 2	1237
6	Company 3	phone 3	1238
7	Company 3	phone 3	1239
Sheet2

 

[Georgiboy]

Perhaps this as a formula option:

Lambda-map examples.xlsx
	M	N	O
4	Company 1	Phone 1	Data 1 Data 2 Data 3 Data 4
5	Company 2	Phone 2	Data 1 Data 2 Data 3 Data 4
6
7
8	Company 1	Phone 1	Data 1
9	Company 1	Phone 1	Data 2
10	Company 1	Phone 1	Data 3
11	Company 1	Phone 1	Data 4
12	Company 2	Phone 2	Data 1
13	Company 2	Phone 2	Data 2
14	Company 2	Phone 2	Data 3
15	Company 2	Phone 2	Data 4
Sheet3

Cell Formulas
Range	Formula
M8:O15	M8	=LET(a,M4:M5,b,N4:N5,c,O4:O5, x,WRAPROWS(DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(a&", "&b&", ",LEN(c)-LEN(SUBSTITUTE(c,CHAR(10),""))+1)),", "),,-1),2), HSTACK(x,TEXTSPLIT(TEXTJOIN(CHAR(10),TRUE,c),,CHAR(10))))
Dynamic array formulas.