Dividing excel rows into appropriate columns [Closed]

Report
-
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
Hi,
I could really use help on the following with my Excel 2007 file-- I have huge excel files that need to be rearranged into proper format. Currently they are in this format (using a very simplified example):

Column 1 Column 2 Column 3
Person1 socks 3
Person1 pants 4
Person1 ties 10
Person1 hats 6
Person2 socks 2
Person2 pants 3
Person2 ties 1
Person2 hats 7
Person3...


The format I would like to have is as follows:
Column 1 Column 2 Column 3 Column 4 Column 5....
Person1 Person2 Person3 Person4
socks 3 2
pants 4 3
ties 10 1
hats 6 7

... and so forth.

I'm not very handy with Excel and am wondering is there anyway I can automate this task to save myself a lot of cutting and pasting? I have huge amounts of data, it's akin to processing 500 people with over 10,000 clothing items.
Thank you so much in advance.... this would really save me weeks and weeks of time.

1 reply

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
You'll need to insert a pivot table to do this.
Create a pivot table with Person as a column label, clothing as a Row lables and the number as a Value. it'll give you this (hopefully the format is kept).
Sum of Number	Column Labels		
Row Labels	Person1	Person2	Grand Total
                          hats	6	7	13
                          pants	4	3	7
                          socks	3	2	5
                          ties	10	1	11
           Grand Total	23	13	36
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3984 users have said thank you to us this month