I have been trying to separate the information of an Excel sheet in parts, it looks something like this:
I need it to look like this:
I used this VBA script, but it doesn't work since it replicates the first value it finds in the D column (t) for as many objects it finds in the cell (4 in the case of 1st row):
Sub SplitPartsRows()
Dim rng As Range
Dim r As Long
Dim arrParts() As String
Dim partNum As Long
'## In my example i use columns A:E, and column D contains the Corresponding Parts ##
Set rng = Range("A1:AA16000") '## Modify as needed ##'
r = 2
Do While r <= rng.Rows.Count
'## Split the value in column D (4) by commas, store in array ##
arrParts = Split(rng(r, 4).Value, ",")
'## If there's more than one item in the array, add new lines ##
If UBound(arrParts) >= 1 Then '## corrected this logic for base 0 array
rng(r, 4).Value = arrParts(0)
'## Iterate over the items in the array ##
For partNum = 1 To UBound(arrParts)
'## Insert a new row ##'
'## increment the row counter variable ##
r = r + 1
rng.Rows(r).Insert Shift:=xlDown
'## Copy the row above ##'
rng.Rows(r).Value = rng.Rows(r - 1).Value
'## update the part number in the new row ##'
rng(r, 4).Value = Trim(arrParts(partNum))
'## resize our range variable as needed ##
Set rng = rng.Resize(rng.Rows.Count + 1, rng.Columns.Count)
Next
End If
'## increment the row counter variable ##
r = r + 1
Loop
End Sub
The result would be something like this:
I don't necessarily need to use VBA Script I accept any possible answer, even if in google appscript
Bring the data into powerquery with data ... from table/range ...
Right click the rightmost column and choose Split Column ... by delimiter...
Choose comma as the delimiter and in advanced options choose rows
File .. close and load ... back to excel