Tranpose data from columns to rows

[heyredone]

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.

 

[Lita]

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.

 

[Luke M]

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.

 

[Tushar Mehta]

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

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

 

[Shane Devenshire]

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
D210 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