Data Format Chage

[zuh_143]

Hello Guys!
My requirements are as under:-
[TABLE="width: 597"]
<tbody style="border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased;">[TR]
[TD="colspan: 2"]Current Data Form[/TD]
[/TR]
[TR]
[TD]CustCode[/TD]
[TD]ItemCode[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400005[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 597"]
<tbody style="border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased;">[TR]
[TD="colspan: 2"]Required Data Form[/TD]
[/TR]
[TR]
[TD]CustCode[/TD]
[TD]ItemCode[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]400005[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]400005[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]400005[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]400005[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400001[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400002[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400003[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400004[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]400005[/TD]
[/TR]
</tbody>[/TABLE]

 

[asyed]

I am assuming Row 1 is headings (Cust Code, Item code)
Enter 101 in A2, and 400001 in B2.
In A3 enter the following formula and copy down:
=IF(COUNTIF($A$1:A2,A2)>4,A2+1,A2)
In B3 enter the following formula and copy down:
=IF(A3=A2,B2+1,400001)

 

[Rick Rothstein]

Are you wanting to overwrite your existing table of values with the expanded one? If so, give this macro a try...

Sub RedistributeCustCodesAndItemCodes()
  Dim X As Long, Index As Long, CustCodes As Variant
  CustCodes = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    .Copy Range("B2:B" & .Rows.Count * UBound(CustCodes) + 1)
    For X = 2 To .Rows.Count * UBound(CustCodes) + 1
      If Cells(X, "B").Value = Range("B2").Value Then
        Index = Index + 1
        Cells(X, "A").Value = CustCodes(Index, 1)
      Else
        Cells(X, "A").Value = Cells(X - 1, "A").Value
      End If
    Next
  End With
End Sub