Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Transpose columns to rows using first columns repeated.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Transpose columns to rows using first columns repeated.
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 747
Default Transpose columns to rows using first columns repeated.
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



On Feb 12, 12:04*pm, hn7155 wrote:
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default Transpose columns to rows using first columns repeated.
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
---
"hn7155" wrote:
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,346
Default Transpose columns to rows using first columns repeated.
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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"hn7155" wrote:

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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Transpose columns to rows using first columns repeated.
Worked like a charm, thanks for the quick response.

"Max" wrote:

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
---
"hn7155" wrote:
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Transpose columns to rows using first columns repeated.
Thanks for the quick response.

"muddan madhu" wrote:

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



On Feb 12, 12:04 pm, hn7155 wrote:
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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Transpose columns to rows using first columns repeated.
Thanks for the quick response, I used Max's solution.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"hn7155" wrote:

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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default Transpose columns to rows using first columns repeated.
Welcome. Thanks for feeding back, and the high-five !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"hn7155" wrote in message
...
Worked like a charm, thanks for the quick response.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
repeating columns containing rows going beyond the repeated column annoni Excel Discussion (Misc queries) 0 June 25th 08 03:01 PM
How to make columns based on repeated rows?? oli merge Excel Discussion (Misc queries) 4 December 18th 07 11:36 AM
How do you transpose rows to columns? msn Excel Discussion (Misc queries) 6 September 1st 07 04:00 AM
how do I transpose columns and rows jnix Excel Discussion (Misc queries) 10 December 22nd 04 01:44 PM
repeated transpose from rows to columns with unequal groups kraymond Excel Discussion (Misc queries) 3 December 20th 04 02:39 PM


All times are GMT +1. The time now is 10:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement