Format Excel data

[jike_s]

Hi all-

I wanted to reformat my data from this:

Item Qty 1-Jan 8-Jan 15-Jan 22-Jan
ABC1 1000 77 77 77 77
ABC2 500 38 38 38 38
ABC3 10000 769 769 769 769

to this:

Item Date Qty
ABC1 1-Jan 77
ABC1 8-Jan 77
ABC1 15-Jan 77
ABC1 22-Jan 77
ABC2 1-Jan 38
ABC2 8-Jan 38
ABC2 15-Jan 38
ABC2 22-Jan 38
ABC3 1-Jan 769
ABC3 8-Jan 769
ABC3 15-Jan 769
ABC3 22-Jan 769

I'll appreciate any help on this.

thanks.

 

[acw]

Hi

This assumes that the data is in column A:F and the output is able to go into columns H:J.

Sub ddd()
  Range("H1:J1").Value = Array("Item", "Date", "Qty")
  For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 3 To 6
      nextrow = Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
      Cells(nextrow, 8).Value = ce.Value
      Cells(nextrow, 9).Value = Cells(1, i).Value
      Cells(nextrow, 10).Value = Cells(ce.Row, i).Value
    Next i
  Next ce
End Sub

HTH

Tony

 

[jike_s]

Thank you.

What if I want the output on a different sheet? Basically, I want to reformat sheet 1 and have the output on sheet 2. Thanks again.

 

[acw]

Hi

Try this.

Sub ddd()
  Dim OutSh As Worksheet
  Set OutSh = Sheets("Sheet2")
  Sheets("sheet1").Select
  OutSh.Range("A1:C1").Value = Array("Item", "Date", "Qty")
  For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 3 To 6
      With OutSh
        nextrow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        .Cells(nextrow, 1).Value = ce.Value
        .Cells(nextrow, 2).Value = Cells(1, i).Value
        .Cells(nextrow, 3).Value = Cells(ce.Row, i).Value
      End With
    Next i
  Next ce
End Sub

It assumes that sheet2 exists.

Tony

 

[jike_s]

perfect! thanks.