Modify a line into several lines

Should be updated now

Originally Posted by jaslake

Hi julien4266

This code is included in the attached workbook (your sample data).

Option Explicit
Public Sub Test()
    Dim rng As Range
    Dim LR As Long
    Dim i As Integer
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("E2:E" & LR)
    With rng
        For i = LR To 1 Step -1
            If rng(i).Value > 1 Then
                rng(i).Offset(1, 0).Resize(rng(i).Value - 1, 1).EntireRow.Insert
                rng(i).Offset(0, 0).EntireRow.Copy
                rng(i).Offset(1, -4).Resize(rng(i).Value - 1, 1).PasteSpecial
            End If
        Next
    End With
 
    Call Test1
 
    Application.ScreenUpdating = True
End Sub
Sub Test1()
    Dim Arr As Variant
    Dim i As Integer
    Dim rng As Range
    Dim fCell As Range
    Dim LR As Long: LR = Range("K" & Rows.Count).End(xlUp).Row
    Set rng = Range("K2:K" & LR)
    With rng
        For Each fCell In rng
            If fCell.Offset(1, -1) <> fCell.Offset(0, -1) Then GoTo Skip
            Arr = Split(fCell.Value, ",")
            For i = LBound(Arr) To UBound(Arr)
                fCell.Offset(i, 0) = LTrim(Arr(i))
            Next i
Skip:
        Next fCell
    End With
End Sub

It appears to do what you requested. Let me know of issues.

John

Thanks that's very close to what i want to do. Do you think it's possible to change the quantity to 1 for all lines after the process?
The other thing will be to put Spare as a default value if their is no TAG in this way if their is several spare in one line at the beginning, we don't have to write it.
Thanks a lot already for what you have done. It's already very good.

Originally Posted by jaslake

Hi julien4266
With the code in the attached workbook, if you have more units than you have tags, the units without tags are assigned a tag value of "Spare" and all unit quantities are changed to 1.
Let me know of issues.
John

At the first sight it seems perfect to me. Thanks you very much for your help.
Well done.

Originally Posted by jaslake

Hi julien4266

It appears there's a problem with line 10 of your original data. You have a Qte of 5 but a TAG of only 4. I'm going to assume the extra item is a Spare but it shouldn't matter. However, these must match or you'll get incorrect data.

I may post an offered solution soon.

John

Hey John
In this case we have qte 5, 4 item are tagged at the beginning because we know where they go and which tag they have. The last item will go as a spare part.
The idea is if we have for exemple qty 6 and only 3 TAGs we will have 6 lines. Three of them with the dedicated TAG and the others three will go as Spare parts. But in the cell TAG spare will be written only once

Thanks for your help