Data Manipulation needed to populate a Pivot table
Ken Wright
more) are just names and descriptions, and the data from Oct 02 outwards is
a number to represent how much of their time per month (1=100%) will be
spent on that task
Lab1 Lab2 Lab3 Oct02 Nov02 Dec02 Jan03 and so
on........................
Jon abc bbc 1 1.1 1 0.9
Bill bcd ccd 0.8 1.2 0.9 1
Fred cde dde 0.2 0.8 1 1
Sal efg ffg 0.4 0.5 0.4 0.4
Each person can have multiple entries. I know he can pull his data into
various reports with Autofilter, and this is what I suggested, however, he
would like to pull all the data into a Pivot table and be able to do all the
wizzy things he has seen a pivot table do. To this end he would like to
effectively create a database listing so that his data looks like the table
below. This means he would be transposing the numerical data for each
individual existing row and then creating a new row for every months
numerical entry, putting the existing textual data against each new row for
every person.
Lab1 Lab2 Lab3 Mth Amt
Jon abc bbc Oct02 1
Jon abc bbc Nov02 1.1
Jon abc bbc Dec02 1
Jon abc bbc Jan03 0.9
(Spaces to aid legibility
only)
Bill bcd ccd Oct02 0.8
Bill bcd ccd Nov02 1.2
Bill bcd ccd Dec02 1
Bill bcd ccd Jan03 1
(Spaces to aid legibility
only)
Fred cde dde Oct02 0.2
Fred cde dde Nov02 0.8
Fred cde dde Dec02 1
Fred cde dde Jan03 1
and so on.............
This then lets him use the pivot table feature and gives him his subtotals
and so on.
There are hundreds of rows of data for all the records, each of them with
say 24 months of data going across the columns, so assuming there were say
200 rows of data by 24 months, he would be looking to get 200 x 24 rows of
data to populate a pivot table. Just realised as I typed that there are
limitations to a pivot table. Shouldn't be a problem at the moment though
(I think).
Questions:-
1) Is there a way to use the data as is to populate a pivot table?
2) If not, is there an easy way to get the data in the format he wants it,
either formulaic or macro based?
Many thanks
Ken....................
Ken Wright
appreciated (And to John also for publishing the tip!!).
PS Holiday was superb - Did NOT want to come back to the UK - Been depressed
ever since I returned (Wife has helped me there..), and although I have been
trying for a little while, I am now doing my absolute damndest to try and
pull off an assignment over there for 2/3 years :-)
Best Wishes
Ken.........................
"William" <willw...@yahoo.com> wrote in message
news:O4SCr5ciCHA.1364@tkmsftngp11...
> Hi Ken
>
> I think you need to go here!
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> --
>
> Regards
>
> William
> willw...@yahoo.com
>
>
> "Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
> news:eCG$KuciCHA.2156@tkmsftngp12...
William
<< Did NOT want to come back to the UK >>
Why not - weather is great down South.........NOT! <vbg>
--
Regards
William
willw...@yahoo.com
"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:eoOXSAdiCHA.3708@tkmsftngp08...
William
I think you need to go here!
http://j-walk.com/ss/excel/usertips/tip068.htm
--
Regards
William
willw...@yahoo.com
"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:eCG$KuciCHA.2156@tkmsftngp12...