Transpose columns to rows using first columns repeated.


H

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
 
Ad

Advertisements

M

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
 
M

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 A2:D2 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
 
S

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.
 
H

hn7155

Thanks for the quick response.

muddan madhu said:
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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top