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

 

[ellison]

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.

Lovely, just ordered that ... early birthday present for myself!!!

 

[sandy666]

I'll keep digging!

here is almost everything about Power Query

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[JLGWhiz]

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

What us the error message? It ran without error for me.

 

[ellison]

What us the error message? It ran without error for me.

Hi....

"run-time error "9":
subscript out of range"

Info as below on Sheet1: (No results sheet added)

re_rog_data_power_query_alt_play.xlsm
	A	B
1	Line_ID	Data
2	4	orange;black,54632
3	12	black;Grey
4	81	2222_4;WHITE;red
Sheet1

Many, many thanks!

 

[JLGWhiz]

D

Hi....

"run-time error "9":
subscript out of range"

Info as below on Sheet1: (No results sheet added)



Many, many thanks!

If you don't have a sheet second sheet in that workbook, it will produce that error. You can add sheet 2, if that is the case. The error 9 occurs when the code tells the compiler to look for an object (range, sheet, workbook, control) and the object does not exist or the name is misspelled.

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), ",")
       On Error Resume Next
       If IsError(Sheets(2)) Then
           Sheets.Add After:=Sheets(1)
        End If
        On Error GoTo 0
        Err.Clear
        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]

D

If you don't have a sheet second sheet in that workbook, it will produce that error. You can add sheet 2, if that is the case. The error 9 occurs when the code tells the compiler to look for an object (range, sheet, workbook, control) and the object does not exist or the name is misspelled.

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), ",")
       On Error Resume Next
       If IsError(Sheets(2)) Then
           Sheets.Add After:=Sheets(1)
        End If
        On Error GoTo 0
        Err.Clear
        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 your reply......

But I've got a gremlin in here somewhere, grr!

Have loaded up info onto mr_excel_reorg_test_02.xlsm

Sheet 1:

mr_excel_reorg_test_02.xlsm
	A	B
1	Line_ID	Data
2	4	orange;black,54632
3	12	black;Grey
4	81	2222_4;WHITE;red
Sheet1

Then I loaded up the second version of the code.

And I tried running it with (& without) creating sheet2.

I get the same set of results:

Sheet2:

mr_excel_reorg_test_02.xlsm
	A	B
1
2	4	orange;black
3	4	orange;black
4	12	black;Grey
5	81	2222_4;WHITE;red
sheet2

Huge apologies, any idea where I may be going wrong?!

 

[JLGWhiz]

For one thing, the separators are not consistent. You have both commas and semicolons. The code is based on only commas.

But I see that the code does not fully produce what you want so I will again modify it.

 

[JLGWhiz]

this seems to work like you want, using all commas as separators.

Sub t2()
Dim i As Long, spl As Variant
With Sheets(1)
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        spl = Application.Transpose(Split(.Cells(i, 2), ","))
       On Error Resume Next
       If IsError(Sheets(2)) Then
           Sheets.Add After:=Sheets(1)
        End If
        On Error GoTo 0
        Err.Clear
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl)) = .Cells(i, 1).Value
        Sheets(2).Cells(Rows.Count, 2).End(xlUp)(2).Resize(UBound(spl)) = spl
    Next
End With
End Sub

Here is the data on sheet one.

Discussions.xlsm
	A	B
1	Line_ID	Data
2	4	orange,black,54632
3	12	black,Grey
4	81	2222_4,WHITE,red
Sheet1

and here is the results on sheet 2.

Discussions.xlsm
	A	B
1
2	4	orange
3	4	black
4	4	54632
5	12	black
6	12	Grey
7	81	2222_4
8	81	WHITE
9	81	red
Sheet2

 

[ellison]

this seems to work like you want, using all commas as separators.

Sub t2()
Dim i As Long, spl As Variant
With Sheets(1)
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        spl = Application.Transpose(Split(.Cells(i, 2), ","))
       On Error Resume Next
       If IsError(Sheets(2)) Then
           Sheets.Add After:=Sheets(1)
        End If
        On Error GoTo 0
        Err.Clear
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl)) = .Cells(i, 1).Value
        Sheets(2).Cells(Rows.Count, 2).End(xlUp)(2).Resize(UBound(spl)) = spl
    Next
End With
End Sub

Here is the data on sheet one.

Discussions.xlsm
	A	B
1	Line_ID	Data
2	4	orange,black,54632
3	12	black,Grey
4	81	2222_4,WHITE,red
Sheet1

and here is the results on sheet 2.

Discussions.xlsm
	A	B
1
2	4	orange
3	4	black
4	4	54632
5	12	black
6	12	Grey
7	81	2222_4
8	81	WHITE
9	81	red
Sheet2

Thank you so much and thanks again for persevering!!!

Massively appreciated

 

[JLGWhiz]

Thank you so much and thanks again for persevering!!!

Massively appreciated

You're welcome,
regards, JLG