Reorganise data onto different rows based on separator

[ellison]

Hi, I'm wondering if there's a way that I can reorganise the data as below?

The data in Column B separates the different strings using ";"

And we'd like to separate each string of data out onto its own row, with its original Line ID?

Preferably on a separate results sheet, if at all poss

Line_ID	Data	Results (preferably on a different sheet):	Line_ID	Data_V1
4	orange;black;54632		4	orange
12	black;Grey		4	black
81	2222_4;WHITE;red		4	54632
			12	black
			12	Grey
			81	2222_4
			81	WHITE
			81	red

 

[sandy666]

Line_ID	Data		Line_ID	Data
4	orange;black;54632		4	orange
12	black;Grey		4	black
81	2222_4;WHITE;red		4	54632
			12	black
			12	Grey
			81	2222_4
			81	WHITE
			81	red

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

result table can be loaded wherever you want

 

[ellison]

a-ha a power query!
It's about time I tried to figure out how to use these, will have a play and get back to you.
Wish me luck!

 

[sandy666]

sure

 

[ellison]

sure

Hi, can I just ask.. how do I enter this onto the powerquery?

So far, I've created a fresh workbook, set up a powerquery, chosen the file with the table of original data (as above) and now I can either hit "load" or "transform"...

Am I on the right lines?!?!

 

[sandy666]

nooooo...
you've a table (if this is a range select it then use Ctrl+T to make it Excel Table)
then on Data tab find From Table
it will open PQ Editor
then

 

[JLGWhiz]

Some VBA if you can't get the query to work for you.

Sub t()
Dim i As Long, spl As Variant
With ActiveSheet
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        spl = Split(.Cells(i, 2), ",")
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl) + 1) = .Cells(i, 1).Value
        Sheets(2).Cells(Rows.Count, 2).End(xlUp)(2).Resize(UBound(spl) + 1) = spl
    Next
End With
End Sub

 

[ellison]

Some VBA if you can't get the query to work for you.

Sub t()
Dim i As Long, spl As Variant
With ActiveSheet
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        spl = Split(.Cells(i, 2), ",")
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl) + 1) = .Cells(i, 1).Value
        Sheets(2).Cells(Rows.Count, 2).End(xlUp)(2).Resize(UBound(spl) + 1) = spl
    Next
End With
End Sub

Huge thanks for this....

I'm getting an error when I run this and when I hit debug, the line it highlights:

Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl) + 1) = .Cells(i, 1).Value

 

[ellison]

nooooo...
you've a table (if this is a range select it then use Ctrl+T to make it Excel Table)
then on Data tab find From Table
it will open PQ Editor
then
View attachment 30355

That's brilliant, I'll keep digging!
Huge thanks

 

[alansidman]

ellison
A really good primer is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar available in the Mr E store and Amazon.