Format Excel data

jike_s

New Member
Joined
Oct 23, 2006
Messages
5
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

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

Code:
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

New Member
Joined
Oct 23, 2006
Messages
5
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try this.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,656
Members
410,627
Latest member
georgealice
Top