G
Guest
Hello,
I have 2 worksheets with data. Both worksheets have the same 2 first colums,
"project number" (column A) and "activity" (column B). The first worksheet
has one other column (column C) with data about the activity from column B of
the project from column A. The second worksheet has four other colums
(columns C through F) with data about the activity from column B of the
project from column A. The data about the specific project and activity are
numbers (hours spent).
The first worksheet looks like this:
Proj. Activity Estimated
1 Act.A 8
1 Act.B 4
2 Act.B 5
2 Act.A 2
etc.
The second worksheet looks quite the same, like this:
Proj. Activity Planned Written Difference
1 Act.A 4 5 -1
1 Act.B 0 3 1
1 Act.B 3 0 0
2 Act.A 0 5 3
etc.
Note that the number of times a certain project and/or activity appears may
be different in both worksheets.
Now what I want to do is to make a third worksheet (a list or Pivot Table,
it doesn't matter in which form) with totals of the data from the other
worksheets in the following form:
Proj. Activity Estimated Planned Written Difference
1 Act.A 8 4 5 -1
1 Act.B 4 3 3 1
2 Act.A 2 0 5 3
2 Act.B 5 0 0 0
etc.
So in this last worksheet, every activity only shows once per project, and
the data in columns C through F are totals of all instances of Act.A of
project 1, and Act.B of project 1, Act.A of project 2, etc.
I hope it's clear what I mean, if not please ask and I'll try to explain
more...
I thought it might be possible to make a Pivot Table of the first worksheet,
and for each row doing a "multiple level vlookup" (if at all possible) of the
project number and activity in that row, in the second worksheet and sum up
the results. But I don't have a clue if this is possible and if yes, how to
do it. I hope someone can tell me, or maybe someone has a better idea?
Thanks in advance!
Oscar
I have 2 worksheets with data. Both worksheets have the same 2 first colums,
"project number" (column A) and "activity" (column B). The first worksheet
has one other column (column C) with data about the activity from column B of
the project from column A. The second worksheet has four other colums
(columns C through F) with data about the activity from column B of the
project from column A. The data about the specific project and activity are
numbers (hours spent).
The first worksheet looks like this:
Proj. Activity Estimated
1 Act.A 8
1 Act.B 4
2 Act.B 5
2 Act.A 2
etc.
The second worksheet looks quite the same, like this:
Proj. Activity Planned Written Difference
1 Act.A 4 5 -1
1 Act.B 0 3 1
1 Act.B 3 0 0
2 Act.A 0 5 3
etc.
Note that the number of times a certain project and/or activity appears may
be different in both worksheets.
Now what I want to do is to make a third worksheet (a list or Pivot Table,
it doesn't matter in which form) with totals of the data from the other
worksheets in the following form:
Proj. Activity Estimated Planned Written Difference
1 Act.A 8 4 5 -1
1 Act.B 4 3 3 1
2 Act.A 2 0 5 3
2 Act.B 5 0 0 0
etc.
So in this last worksheet, every activity only shows once per project, and
the data in columns C through F are totals of all instances of Act.A of
project 1, and Act.B of project 1, Act.A of project 2, etc.
I hope it's clear what I mean, if not please ask and I'll try to explain
more...
I thought it might be possible to make a Pivot Table of the first worksheet,
and for each row doing a "multiple level vlookup" (if at all possible) of the
project number and activity in that row, in the second worksheet and sum up
the results. But I don't have a clue if this is possible and if yes, how to
do it. I hope someone can tell me, or maybe someone has a better idea?
Thanks in advance!
Oscar