Nov 20 2019 05:47 AM
Hello all,
I have an excel sheet in which some rows have multiple lines of text and some have only one line.
I want to convert all those rows which have multiple lines to separate rows.
See example below.
Original table:
Order | Line | Date | Supplier |
1 2 | 1 2 | 1/12/2020 1/25/2020 | Supplier1 Supplier2 |
3 | 3 | 11/17/2019 | Supplier3 |
Converted table:
Order | Line | Date | Supplier |
1 | 1 | 1/12/2020 | Supplier1 |
2 | 2 | 1/25/2020 | Supplier2 |
3 | 3 | 11/17/2019 | Supplier3 |
Any ideas on how to achieve that? preferable without a macro.
Thanks,
Liron.
Nov 26 2019 01:33 AM
@Eizenman I'm guessing the data is pasted into Excel from elsewhere, perhaps it is easier to try a different way to get that data into Excel? What is the source of this table?
Nov 26 2019 01:36 AM
I don't have a way to change how I get the data
Nov 26 2019 01:54 AM
Nov 26 2019 02:21 AM
@Eizenman I'm not sure why you don't want to use VBA, but I couldn't come up with an easy alternative. The code below assumes all cells on a row contain the same number of "lines":
Option Explicit
Sub SplitTableROws()
Dim Data As Variant
Dim NewData As Variant
Dim SplitData As Variant
Dim DataRow As Long
Dim NewDataRow As Long
Dim Col As Long
Dim Line As Long
Data = ActiveSheet.Range("A1").CurrentRegion.Value
NewData = Application.Transpose(Data)
For DataRow = LBound(Data, 1) To UBound(Data, 1)
NewDataRow = NewDataRow + 1
If InStr(Data(DataRow, 1), Chr(10)) > 0 Then
ReDim Preserve NewData(1 To UBound(Data, 2), 1 To NewDataRow + UBound(Split(Data(DataRow, 1), Chr(10))))
For Col = LBound(Data, 2) To UBound(Data, 2)
SplitData = Split(Data(DataRow, Col), Chr(10))
For Line = LBound(SplitData) To UBound(SplitData)
NewData(Col, NewDataRow + Line) = SplitData(Line)
Next
Next
NewDataRow = NewDataRow + Line - 1
Else
ReDim Preserve NewData(1 To UBound(Data, 2), 1 To NewDataRow)
For Col = LBound(Data, 2) To UBound(Data, 2)
NewData(Col, NewDataRow) = Data(DataRow, Col)
Next
End If
Next DataRow
Range("F1").Resize(NewDataRow, UBound(Data, 2)).Value = Application.Transpose(NewData)
End Sub
Nov 26 2019 03:08 AM
@Jan Karel Pieterse I ran it but it just creates duplicated the column headers to the right of the existing columns without splitting the rows.
Am I supposed to take additional steps?
Nov 26 2019 03:49 AM
Nov 26 2019 09:08 AM
One more variant with Power Query. Not everything from user interface, the script is
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UnpivotColumns = Table.UnpivotOtherColumns(
Source,
{},
"Attribute", "Value"
),
DateToText = Table.ReplaceValue(
UnpivotColumns,
each [Value],
each
if Type.Is(Value.Type([Value]),DateTime.Type)
then Date.ToText(DateTime.Date([Value]),"yyyy-MM-dd")
else [Value],
Replacer.ReplaceValue,{"Value"}
),
AllToText = Table.TransformColumnTypes(
DateToText,
{{"Value", type text}}
),
TransformColumn = Table.TransformColumns(
AllToText,
{
{"Value", Splitter.SplitTextByDelimiter("#(lf)")}
}
),
ExpandToRows = Table.ExpandListColumn(
TransformColumn,
"Value"
),
GroupRows = Table.Group(
ExpandToRows,
{"Attribute"},
{{"Group", each [Value]}}
),
Result = Table.FromRows(
List.Zip(GroupRows[Group]),
GroupRows[Attribute]
),
ProperType = Table.TransformColumnTypes(
Result,
{
{"Line", Int64.Type},
{"Date", type date},
{"Supplier", type text}
}
)
in
ProperType