Transpose columns to rows using first columns repeated.

[hn7155]

excel 2007

example

proj est 2/1/2009 3/1/2009 4/1/2009 5/1/2009 6/1/2009 7/1/2009 8/1/2009 9/1/2009 10/1/2009 11/1/2009 12/1/2009 1/1/2010
a b 1 2 3 4 5 6 7 8 9 10 11 12
b x 13 14 15 16 17 18 19 20 21 22 23 24
c y 25 26 27 28 29 30 31 32 33 34 35 36
d w 11 22 33 44 55 66 77 88 99 111 222 333
e v 444 555 666 777 888 999 123 234 345 456 567 678

To:

Proj Est Month Amount
a b 2/1/2009 1
b x 2/1/2009 13
c y 2/1/2009 25
d w 2/1/2009 11
e v 2/1/2009 444
a b 3/1/2009 2
b x 3/1/2009 14
c y 3/1/2009 26
d w 3/1/2009 22
e v 3/1/2009 555
etc to end of x amount of rows and 12 months of columns to the right of
repeated data (columns a, b) in this example

 

[muddan madhu]

assumed you have data in Range A1:N6,

try this macro , output will be same sheet starts from range A10

Sub grouping()

Set rng1 = Range("A2:B6")

rng1.Copy
Range("A10").Select
ActiveSheet.Paste

For i = 3 To 14
Cells(2, i).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Cells(10, 4).Select
If Cells(10, 4).Value = "" Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If

Cells(10, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, 3).Value = ""
rng1.Copy
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Loop

Cells(1, i).Select
Selection.Copy
Cells(10, 3).Select
If Cells(10, 3).Value = "" Then
Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
ActiveSheet.Paste
End If
Next

Application.CutCopyMode = False

End Sub

 

[Max]

One way to deliver it ..

Assume source table as posted is in Sheet1,
cols A to col ..., data from row2 to row6 (5 rows)

In another sheet,
In A2: =OFFSET(Sheet1!A$2,MOD(ROWS($1:1)-1,5),)
Copy A2 to B2

In C2: =INDEX(Sheet1!$C$1:$IV$1,INT((ROWS($1:1)-1)/5)+1)
Format C2 as date

In D2: =OFFSET(Sheet1!$C$2,MOD(ROWS($1:1)-1,5),INT((ROWS($1:1)-1)/5))
Copy A22 down as far as required to exhaust the source data. That should
return the exact results that you seek.

Click the YES button below to high-five this response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Shane Devenshire]

Hi,

Here is a rather cute approach, assume your data is in A1:N6 of sheet1:

1. Choose Data, PivotTable and Pivot Chart Report
2. Pick Multiple consolidation range and click Next twice and highlight the
entire range for the Range and then click Finish
3. Double-click Count of Value as switch to Summarize by Sum
4. Locate the gand total at the far bottom right of the pivot table and
double click it.
5. Delete Column D and insert a blank column between A and B
6. In cell B3 enter =VLOOKUP(A3,Sheet1!$A$2:$B$6,2) and fill it down
7. Select all the data from C3 down to the end of the data in column C.
press F5, Special, Constants, and leave only text check, click OK. Press
Ctrl Minus (Ctrl -) and choose Entire Row.
8. Change the column names, and sort on columns C, A, and B.

 

[hn7155]

Worked like a charm, thanks for the quick response.

 

[hn7155]

Thanks for the quick response.

assumed you have data in Range A1:N6,

try this macro , output will be same sheet starts from range A10

Sub grouping()

Set rng1 = Range("A2:B6")

rng1.Copy
Range("A10").Select
ActiveSheet.Paste

For i = 3 To 14
Cells(2, i).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Cells(10, 4).Select
If Cells(10, 4).Value = "" Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If

Cells(10, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, 3).Value = ""
rng1.Copy
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Loop

Cells(1, i).Select
Selection.Copy
Cells(10, 3).Select
If Cells(10, 3).Value = "" Then
Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
ActiveSheet.Paste
End If
Next

Application.CutCopyMode = False

End Sub

 

[hn7155]

Thanks for the quick response, I used Max's solution.

 

[Max]

Welcome. Thanks for feeding back, and the high-five !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik