Occasional Contributor

Need data in same row. Please find attached.

3 Replies


Try this macro. Please test on a copy of your worksheet.

Sub Sync()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Application.ScreenUpdating = False
    r = 3
        m = 10000
        For c = 1 To 5
            If Cells(r, c).Value <> "" Then
                m = Application.Min(Val(Mid(Cells(r, c).Value, 6)), m)
            End If
        Next c
        For c = 1 To 5
            If Cells(r, c).Value <> "" Then
                If Val(Mid(Cells(r, c).Value, 6)) > m Then
                    Cells(r, c).Insert Shift:=xlShiftDown
                End If
            End If
        Next c
        r = r + 1
        If r = 15 Then Stop
    Loop Until Application.CountA(Cells(r, 1).Resize(1, 5)) = 0
    Application.ScreenUpdating = True
End Sub

@Jagdeep_ As a variant, similar result with Power Query. File attached.

Screenshot 2020-12-17 at 15.08.09.png


With formulas as variant that could be


From you sample is not clear shall we keep empty rows (as between MANA-0019 and MANA-0028) or shall ignore them like between MANA-0006 and MANA-0012. Assuming we keep empty and your actual data has exactly the same structure, i.e. 4-digit of sequential numbers for each value, formula in first column could be


and drag it to the right.