So... i have a sheet with a lot of lines and for each line I have between 0 and 5 information's (one in its own column) i need to spread out so only 1 column.
Eksample:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]part1[/TD]
[TD]part2[/TD]
[TD]par3[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S009[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
This i need to get to this form:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]part[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]part1[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]Part3[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S009[/TD]
[TD]Part2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]part1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]Part2[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]Part3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I think im ½ the way
First i made a Macro that filled in extra lines based on a count of how may cells in a row had X ( this i do in column P) - So for order S001 I auto generated 1 new line and took all data in other columns and pasted it down:
My problem now is ... how to i make it take part 1 ... skip the blank Part 2.. and then take part 3 ?
Eksample:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]part1[/TD]
[TD]part2[/TD]
[TD]par3[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S009[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
This i need to get to this form:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]part[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]part1[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S001[/TD]
[TD]Part3[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S009[/TD]
[TD]Part2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]part1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]Part2[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S011[/TD]
[TD]Part3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I think im ½ the way
First i made a Macro that filled in extra lines based on a count of how may cells in a row had X ( this i do in column P) - So for order S001 I auto generated 1 new line and took all data in other columns and pasted it down:
Code:
Sub Insert() Dim End_Row As Long, n As Long, Ins As Long
End_Row = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For n = End_Row To 1 Step -1
Ins = Cells(n, "P").Value
If Ins > 1 Then
Range("P" & n + 1 & ":P" & n + Ins - 1).EntireRow.Insert
Range(Cells(n, 1), Cells(n + Ins - 1, "P")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "Q")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "R")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "S")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "T")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "U")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "V")).FillDown
' Det her virker desvære ikke
' ElseIf Ins < 0 Then
' Range("P" & n + 1 & ":P" & n + Ins - 1).EntireRow.Delete
End If
Next n
Application.ScreenUpdating = True
End Sub
My problem now is ... how to i make it take part 1 ... skip the blank Part 2.. and then take part 3 ?
Last edited: