Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tranpose data from columns to rows
I need help with transposing data from columns to rows. I have a worksheet
as follows (sample section): A B C 1 NAME YEAR NO. OF WORKWEEKS 2 Person A 2003 24 3 Person A 2004 50 4 Person A 2005 49 5 Person A 2006 50 6 Person A 2007 52 7 Person A 2008 45 8 Person B 2008 2 9 Person C 2005 1 10 Person D 2003 15 11 Person D 2004 2 12 Person E 2004 17 13 Person E 2005 0 14 Person E 2006 17 15 Person E 2007 31 I need the No. of Workweek data for Person A to spread across 1 row beginning at E2 (I have other data in column D), Person B's data across 1 row, and so on. Some employees have Workweek data for years 2003 through 2008 (such as Person A), some only have data for one year or some of the years (such as Person B and Person D). Can someone help me with an array that will work? Thanks so much. -- heyredone |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tranpose data from columns to rows
you can select the first group "24 down to 45" then COPY
Then go to E2 right click Paste Special- Values check of Transpose That should do it. "heyredone" wrote: I need help with transposing data from columns to rows. I have a worksheet as follows (sample section): A B C 1 NAME YEAR NO. OF WORKWEEKS 2 Person A 2003 24 3 Person A 2004 50 4 Person A 2005 49 5 Person A 2006 50 6 Person A 2007 52 7 Person A 2008 45 8 Person B 2008 2 9 Person C 2005 1 10 Person D 2003 15 11 Person D 2004 2 12 Person E 2004 17 13 Person E 2005 0 14 Person E 2006 17 15 Person E 2007 31 I need the No. of Workweek data for Person A to spread across 1 row beginning at E2 (I have other data in column D), Person B's data across 1 row, and so on. Some employees have Workweek data for years 2003 through 2008 (such as Person A), some only have data for one year or some of the years (such as Person B and Person D). Can someone help me with an array that will work? Thanks so much. -- heyredone |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tranpose data from columns to rows
Assume current data is on Sheet1
On new sheets: If names are in column A, and years are in column B formula in B2: =SUMPRODUCT((Sheet1!$A$2:$A$15=$A1)*(Sheet1!$B$1:$ B$15=B$1)*(Sheet1:$C$1:$C$15)) This will work even if you have more than one entry for the same person, same year. You should be able to copy this formula out to fill your array. Note that the sumproduct arrays must be of equal lengths. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "heyredone" wrote: I need help with transposing data from columns to rows. I have a worksheet as follows (sample section): A B C 1 NAME YEAR NO. OF WORKWEEKS 2 Person A 2003 24 3 Person A 2004 50 4 Person A 2005 49 5 Person A 2006 50 6 Person A 2007 52 7 Person A 2008 45 8 Person B 2008 2 9 Person C 2005 1 10 Person D 2003 15 11 Person D 2004 2 12 Person E 2004 17 13 Person E 2005 0 14 Person E 2006 17 15 Person E 2007 31 I need the No. of Workweek data for Person A to spread across 1 row beginning at E2 (I have other data in column D), Person B's data across 1 row, and so on. Some employees have Workweek data for years 2003 through 2008 (such as Person A), some only have data for one year or some of the years (such as Person B and Person D). Can someone help me with an array that will work? Thanks so much. -- heyredone |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tranpose data from columns to rows
Create a PivotTable (2003: Tools | PivotTable and PivotChart
Report...; 2007: Insert Tab | PivotTable button) using your data as the data source. Put the name as a row field, the year as a column field, and No.of workweeks as the data field. If Excel chooses the COUNT function for the data field, change it to SUM. If and when you change the data source, just refresh the PT. On Thu, 20 Nov 2008 13:05:02 -0800, heyredone wrote: I need help with transposing data from columns to rows. I have a worksheet as follows (sample section): A B C 1 NAME YEAR NO. OF WORKWEEKS 2 Person A 2003 24 3 Person A 2004 50 4 Person A 2005 49 5 Person A 2006 50 6 Person A 2007 52 7 Person A 2008 45 8 Person B 2008 2 9 Person C 2005 1 10 Person D 2003 15 11 Person D 2004 2 12 Person E 2004 17 13 Person E 2005 0 14 Person E 2006 17 15 Person E 2007 31 I need the No. of Workweek data for Person A to spread across 1 row beginning at E2 (I have other data in column D), Person B's data across 1 row, and so on. Some employees have Workweek data for years 2003 through 2008 (such as Person A), some only have data for one year or some of the years (such as Person B and Person D). Can someone help me with an array that will work? Thanks so much. Regards, Tushar Mehta Microsoft MVP Excel 2000-2008 www.tushar-mehta.com Tutorials and add-ins for Excel, PowerPoint, and other products |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tranpose data from columns to rows
Hi,
Based on your post if you enter the following formula in E2 and copy it down and then over =SUMPRODUCT(--($A$2:$A$15=$D2),--($B$2:$B$15=E$1),$C$2:$C$15) Where the you list in in columns A:C, you have a unique list of Names in D2:D10 and the years are in E1:J1. However, I have to second the suggestion of using a pivot table, with names in the row area, years in the column area and weeks in the data area. If this helps, please click the Yes button, Cheers, Shane Devenshire "heyredone" wrote: I need help with transposing data from columns to rows. I have a worksheet as follows (sample section): A B C 1 NAME YEAR NO. OF WORKWEEKS 2 Person A 2003 24 3 Person A 2004 50 4 Person A 2005 49 5 Person A 2006 50 6 Person A 2007 52 7 Person A 2008 45 8 Person B 2008 2 9 Person C 2005 1 10 Person D 2003 15 11 Person D 2004 2 12 Person E 2004 17 13 Person E 2005 0 14 Person E 2006 17 15 Person E 2007 31 I need the No. of Workweek data for Person A to spread across 1 row beginning at E2 (I have other data in column D), Person B's data across 1 row, and so on. Some employees have Workweek data for years 2003 through 2008 (such as Person A), some only have data for one year or some of the years (such as Person B and Person D). Can someone help me with an array that will work? Thanks so much. -- heyredone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to tranpose data to designated cells ? | Excel Discussion (Misc queries) | |||
Arrange data spanning 8 columns and 3 rows to 24 columns and 1 row | Excel Discussion (Misc queries) | |||
Excel 2002: Any formula to tranpose columns into rows ? | Excel Discussion (Misc queries) | |||
Tranpose? | Excel Discussion (Misc queries) | |||
Open txt file with more than 256 columns (how to tranpose in rows)? | Excel Discussion (Misc queries) |