Dec 17 2020 05:26 AM
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 Do 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
Dec 17 2020 11:13 AM
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
=XLOOKUP( SEQUENCE(MAX(--RIGHT(Table1,4)),,MIN(--RIGHT(Table1,4))), --RIGHT(XLOOKUP(N$2,Table1[#Headers],Table1),4), XLOOKUP(N$2,Table1[#Headers],Table1),"")
and drag it to the right.