split wrapped cell serial numbers into new lines and allocate description

[cr7]

Hi , have a major problem here with some badly formatted data.

So I have a description of an item and then the item has multiple references against it i.e order reference - these are all on separate lines in the same row!! wrap texted!! really annoying and bad formatting

example table

description
blue	1003 1005 1006
yellow	2550 3440 4444

<tbody>
</tbody>

So the issue I have is I cant do vlookup as I want to put this data in the following format

blue	1003
blue	1005
blue	1006

<tbody>
</tbody>

so each line item has 1 cell, one item reference and 1 description

Is this even possible in excel?

Many thanks in advance this is doing my head in!

cheers

 

[István Hirsch]

AWESOME!!

only issue I see now is that I have 4 columns actually not just A and B therefore the script is overwriting my clumns c and d?

can we get the data to populate in columns e and f?

so I would want all 4 columns to populate rather than the 2?

sorry probably a rookie question tried tweaking the code got errors

If you want the result in columns e and f, change "4" and "3" in the code above to "6" and "5", resp.
If there are further columns to process, please show us one row of data with all the columns and the desired layout.

 

[cr7]

If you want the result in columns e and f, change "4" and "3" in the code above to "6" and "5", resp.
If there are further columns to process, please show us one row of data with all the columns and the desired layout.

desc	sn	cost	quantity
blue	1003 1004 1005	$55	3
yello	2005 2006 2007	$33	3

<tbody>
</tbody>

so I would want the cost to fill down 3 times and also the quantity to fill down 3 times in the same way as the description

hope this is clear

many thanks appreciate the help here

 

[István Hirsch]

Try this:

Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
        x = Split(c, Chr(10))
            For i = 0 To UBound(x)
                Cells(2 + y + i, 7) = x(i)
                Cells(2 + y + i, 6) = Cells(2 + k, 1)
                Cells(2 + y + i, 8) = Cells(2 + k, 3)
                Cells(2 + y + i, 9) = Cells(2 + k, 4)
                
            Next
            y = y + i: k = k + 1
    Next
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	description		cost	quantity
2	blue	100310041005	$55	3		blue	1003	$55	3
3	yellow	255034404444	$66	5		blue	1004	$55	3
4						blue	1005	$55	3
5						yellow	2550	$66	5
6						yellow	3440	$66	5
7						yellow	4444	$66	5
Sheet1

 

[cr7]

strange my results are not splitting the B column

 

[István Hirsch]

strange my results are not splitting the B column

The cause of the difference can be that I separated the strings in cells in column B with soft returns (Alt + J). Try this:

Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
         c = Trim(c)
         x = Split(c)
            For i = 0 To UBound(x)
                Cells(2 + y + i, 7) = x(i)
                Cells(2 + y + i, 6) = Cells(2 + k, 1)
                Cells(2 + y + i, 8) = Cells(2 + k, 3)
                Cells(2 + y + i, 9) = Cells(2 + k, 4)
                
            Next
            y = y + i: k = k + 1
    Next
End Sub

 

[cr7]

The cause of the difference can be that I separated the strings in cells in column B with soft returns (Alt + J). Try this:

Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
         c = Trim(c)
         x = Split(c)
            For i = 0 To UBound(x)
                Cells(2 + y + i, 7) = x(i)
                Cells(2 + y + i, 6) = Cells(2 + k, 1)
                Cells(2 + y + i, 8) = Cells(2 + k, 3)
                Cells(2 + y + i, 9) = Cells(2 + k, 4)
                
            Next
            y = y + i: k = k + 1
    Next
End Sub

Perfect!!

Thank you very much, really appreciate the help with this!

 

[István Hirsch]

You are welcome.