Help with Separating into Rows

[alexfwalker81]

I have a table which has the following structure, where the Data column has pipe separated values which I need to separate into individual rows;

SKU	Data
N000702-EN-GB	105674|130351|14398|14421|19183|19184|19894|3692
N000542-EN-GB	17816|17817|17818|19303|21092

Plus many more rows...

This is what I need to get to, where the pipe separated values exist on a single row, associated with their SKU.

SKU	Data
N000702-EN-GB	105674
N000702-EN-GB	130351
N000702-EN-GB	14398
N000702-EN-GB	14421
N000702-EN-GB	19183
N000702-EN-GB	19184
N000702-EN-GB	19894
N000702-EN-GB	3692
N000542-EN-GB	17816
N000542-EN-GB	17817
N000542-EN-GB	17818
N000542-EN-GB	19303
N000542-EN-GB	21092

It's a job I only need to do once, so the solution doesn't need to be especially elegant! If there's an Excel solution that I've overlooked then I'm perfectly open to that as well.

Thanks in advance for any help.

 

[arnelgp]

create a function that will split the Data then save the result in a Temporary table.

 

[alexfwalker81]

Sounds like exactly what I need, but I don't know how to write a function like that.

 

[Trevor G]

In Excel you can use text to columns and use the Pipe as separator. then use Copy and Paste Special and Transpose that will provide the solution you are looking for without an VBA as it is a one off.

 

[alexfwalker81]

In Excel you can use text to columns and use the Pipe as separator. then use Copy and Paste Special and Transpose that will provide the solution you are looking for without an VBA as it is a one off.

Thanks Trevor, you're right, this would solve the problem. It is a one off job, but there are 3,000 lines in my file, which is why I've turned to Access as I can't do what you suggest 3,000 times! Appreciate I didn't indicate quite how big the file is in my original thread.

 

[Gasman]

Sounds like exactly what I need, but I don't know how to write a function like that.

Split function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Gasman]

Thanks Trevor, you're right, this would solve the problem. It is a one off job, but there are 3,000 lines in my file, which is why I've turned to Access as I can't do what you suggest 3,000 times! Appreciate I didn't indicate quite how big the file is in my original thread.

So you link the table in Excel and do the work there. You do it once not once for each row.
When it is transposed, you can link that sheet to Access.

Perhaps time to normalize your data, for the next time?

 

[alexfwalker81]

What's normalizing the data?

 

[Trevor G]

Where is the original data stored (application it comes from) can the application transpose it before you get it?

 

[alexfwalker81]

Where is the original data stored (application it comes from) can the application transpose it before you get it?

No, it's something that I've been emailed I'm afraid.

 

[Trevor G]

I have had a look on Google and this code should do what you want in Excel. The link to the website is shown here as well. I ran a couple of goes extending the sample you showed and it worked ok.

VBA, Parse by "|" and Transpose, Next row

I have the following data in a cells A1 |stack|over|flow| and cells A2.. |today|is|friday How can I delimit this and transpose it into a vertical/column based view view? Delimiting will give m...

stackoverflow.com

Sub splt()
Dim rw As Long, i As Long, rwcnt As Long
i = 1
With Sheets("Sheet1")
    rwcnt = .Cells(.Rows.Count, 2).End(xlUp).Row 'last non-empty row number
    For rw = 1 To rwcnt 'from row 1 till last non-empty row
        For Each Item In Split(.Cells(rw, 2), "|") 'split the string in column 2 from "|"
            If Item <> "" Then ' 'if the splitted part of the string is not empty
                .Cells(i, 4) = .Cells(rw, 1) 'populate column 4 with column 1
                .Cells(i, 5) = Item 'populate column 5 with splitted part of the string
                .Cells(i, 6) = .Cells(rw, 3) 'populate column 6 with column 3
                i = i + 1 ' increase i variable by one to be able to write the next empty row for the next loop
            End If
        Next 'loop to next splitted string
    Next rw 'loop to next row
    .Columns("A:C").EntireColumn.Delete 'when all data is extracted to Columns D-E-F, delete Columns A-B-C and your results will be in Column A-B-C now
End With
End Sub

 

[arnelgp]

here is a demo.
open MainForm form and press the Split button.
copy the code in Module1 and Output table to your db.

on your db, replace "YourtableName" on the code with the
name of your Table to split.

 

[alexfwalker81]

here is a demo.
open MainForm form and press the Split button.
copy the code in Module1 and Output table to your db.

on your db, replace "YourtableName" on the code with the
name of your Table to split.View attachment 118753View attachment 118754

Thank you, much appreciated!

 

[alexfwalker81]

I have had a look on Google and this code should do what you want in Excel. The link to the website is shown here as well. I ran a couple of goes extending the sample you showed and it worked ok.

VBA, Parse by "|" and Transpose, Next row

I have the following data in a cells A1 |stack|over|flow| and cells A2.. |today|is|friday How can I delimit this and transpose it into a vertical/column based view view? Delimiting will give m...

stackoverflow.com

Sub splt()
Dim rw As Long, i As Long, rwcnt As Long
i = 1
With Sheets("Sheet1")
    rwcnt = .Cells(.Rows.Count, 2).End(xlUp).Row 'last non-empty row number
    For rw = 1 To rwcnt 'from row 1 till last non-empty row
        For Each Item In Split(.Cells(rw, 2), "|") 'split the string in column 2 from "|"
            If Item <> "" Then ' 'if the splitted part of the string is not empty
                .Cells(i, 4) = .Cells(rw, 1) 'populate column 4 with column 1
                .Cells(i, 5) = Item 'populate column 5 with splitted part of the string
                .Cells(i, 6) = .Cells(rw, 3) 'populate column 6 with column 3
                i = i + 1 ' increase i variable by one to be able to write the next empty row for the next loop
            End If
        Next 'loop to next splitted string
    Next rw 'loop to next row
    .Columns("A:C").EntireColumn.Delete 'when all data is extracted to Columns D-E-F, delete Columns A-B-C and your results will be in Column A-B-C now
End With
End Sub

Thank you, this worked perfectly too.

 

[DHookom]

I have a table which has the following structure, where the Data column has pipe separated values which I need to separate into individual rows;

SKU	Data
N000702-EN-GB	105674|130351|14398|14421|19183|19184|19894|3692
N000542-EN-GB	17816|17817|17818|19303|21092

Plus many more rows...

This is what I need to get to, where the pipe separated values exist on a single row, associated with their SKU.

SKU	Data
N000702-EN-GB	105674
N000702-EN-GB	130351
N000702-EN-GB	14398
N000702-EN-GB	14421
N000702-EN-GB	19183
N000702-EN-GB	19184
N000702-EN-GB	19894
N000702-EN-GB	3692
N000542-EN-GB	17816
N000542-EN-GB	17817
N000542-EN-GB	17818
N000542-EN-GB	19303
N000542-EN-GB	21092

It's a job I only need to do once, so the solution doesn't need to be especially elegant! If there's an Excel solution that I've overlooked then I'm perfectly open to that as well.

Thanks in advance for any help.

Do you have a table of each unique data values? If so, this could be accomplished with one simple query. For instance a table [Datas]

Data
105674
130351
14398
14421
17816
17817
17818
19183
19184
19303
19894
21092
3692

You could create a query with SQL of:

SELECT SKUs.SKU, Datas.Data
FROM SKUs, Datas
WHERE (((InStr("|" & [SKUs].[Data] & "|","|" & [Datas].[Data] & "|"))>0));