I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Seems like you could just filter your entire sheet, and show just the values of "Needed" in column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I don't
know how many resources will be needed for each one. IN the end, I expect
there may be a hundred or more needs and those need types should be rolled up
into one entry. I need to show 1 line of C++ and the fact that there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows of .75
people. Maybe if the filter can do some kind of grouping or totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just the values of "Needed" in column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Russ,

Then a pivot table will work if you just want the summary: I tried it on your data set (including
blank rows between) and it worked fine. Create a header row in the top row, select all the data
(including the header row), then use Data / Pivot Table, and click through. Drag "Type" to the Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then drag the data button to the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I don't
know how many resources will be needed for each one. IN the end, I expect
there may be a hundred or more needs and those need types should be rolled up
into one entry. I need to show 1 line of C++ and the fact that there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows of .75
people. Maybe if the filter can do some kind of grouping or totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Bernie,

Your Filter suggestion had me thinking the same way. Problem is the data
isn't in perfect cube-like pivot format, and can't get that way. It has
plenty of row gaps and column gaps. It has values mixed in that are really
subsection headers. I don't know how many rows there will be. There will be
a lot of managerial interraction with the worksheet as teams of people play
"what if?" so it needs to keep the labels, gaps, subheaders, etc. As they
are playing 'what if we replace this real person with a "needed"
placeholder', and vice-versa, they will want to see the results of their
what-iffing then and there. No time for them to make a change, then hand it
over to me to cut and paste together a pivot representation. There could
possibly be hundreds of changes as they try to optimize the staffing across
projects.

I think the only way this can be done, given the real-world context, is to
(roughly)

1) loop down column A and collect the rows that contain "needed."
2) go to each row collected and read the value of column B. Build an array
containing each unique value.
3) for every occurence of each unique value, sum the person-time needs for
each month
4) spit out the results of needed skill and total person-time per month for
that skill

Sounds hairy, but I'm sure it can be done. It's just beyond me.



"Bernie Deitrick" wrote:

Russ,

Then a pivot table will work if you just want the summary: I tried it on your data set (including
blank rows between) and it worked fine. Create a header row in the top row, select all the data
(including the header row), then use Data / Pivot Table, and click through. Drag "Type" to the Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then drag the data button to the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I don't
know how many resources will be needed for each one. IN the end, I expect
there may be a hundred or more needs and those need types should be rolled up
into one entry. I need to show 1 line of C++ and the fact that there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows of .75
people. Maybe if the filter can do some kind of grouping or totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

You could use a macro. This macro assumes that you have five columns of data, with

Status What Jan Feb Mar

in A2:E2 as your headers, and that your data doesn't extend more than 10,000 rows, with
miscellaneous stuff mixed in. The basis is "Needed" in column A. (We could write it to take the
exact number of rows into account, but I'm lazy.... ;-))

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim mySht As Worksheet
Dim myPTS As Worksheet

Set mySht = ActiveSheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Source").Delete
Set myPTS = Worksheets.Add(Befo=Sheets(1))
myPTS.Name = "Pivot Source"

With mySht.Range("A2:E10000")
.AutoFilter Field:=1, Criteria1:="Needed"
.SpecialCells(xlCellTypeVisible).Copy myPTS.Range("A2")
.AutoFilter
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & myPTS.Name & "'!R2C1:R10000C5").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status").CurrentPage = _
"Needed"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("What")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jan"), "Sum of Jan", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Feb"), "Sum of Feb", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Mar"), "Sum of Mar", xlSum
Range("B3").Select

With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
End Sub



"xrbbaker" wrote in message
...
Bernie,

Your Filter suggestion had me thinking the same way. Problem is the data
isn't in perfect cube-like pivot format, and can't get that way. It has
plenty of row gaps and column gaps. It has values mixed in that are really
subsection headers. I don't know how many rows there will be. There will be
a lot of managerial interraction with the worksheet as teams of people play
"what if?" so it needs to keep the labels, gaps, subheaders, etc. As they
are playing 'what if we replace this real person with a "needed"
placeholder', and vice-versa, they will want to see the results of their
what-iffing then and there. No time for them to make a change, then hand it
over to me to cut and paste together a pivot representation. There could
possibly be hundreds of changes as they try to optimize the staffing across
projects.

I think the only way this can be done, given the real-world context, is to
(roughly)

1) loop down column A and collect the rows that contain "needed."
2) go to each row collected and read the value of column B. Build an array
containing each unique value.
3) for every occurence of each unique value, sum the person-time needs for
each month
4) spit out the results of needed skill and total person-time per month for
that skill

Sounds hairy, but I'm sure it can be done. It's just beyond me.



"Bernie Deitrick" wrote:

Russ,

Then a pivot table will work if you just want the summary: I tried it on your data set (including
blank rows between) and it worked fine. Create a header row in the top row, select all the data
(including the header row), then use Data / Pivot Table, and click through. Drag "Type" to the
Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then drag the data button to
the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I don't
know how many resources will be needed for each one. IN the end, I expect
there may be a hundred or more needs and those need types should be rolled up
into one entry. I need to show 1 line of C++ and the fact that there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows of .75
people. Maybe if the filter can do some kind of grouping or totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

You can perform a basic formulaic sum if statement to aggregate the
months.
I've placed an example @ http://www.HelpExcel.com/Ether
It's named sumif.xls

Regards,
Eddie
http://www.ExcelHelp.us

Yeah Bernie, you are lazy and I'm a VBA expert.

I've been getting closer by trying to create an interim table. I THINK I
can get things into a sub-set table by using the MATCH, INDEX and INDIRECT
functions. I use match to find the first occurence of "Needed", then keep
track of the found row in another cell, and use that row +1 as the beginning
of the next range, using INDIRECT to build the range and INDEX to grab the
values. If I can make that "ungrouped" sub-set table, then I could use Ed's
summing code to group/total it into one line.

It was actually my trying to explain the issue to you that helped me
envision this approach/attempt. Problem is it is all brute force and ugly as
heck. I'm going to try to finish it off and see what it looks like, then try
to digest your code. Yours would be much more cool. Once in a pivot table
it would be pretty easy to stand it on its head. This will keep me busy for
days. Thanks very much.



"Bernie Deitrick" wrote:

You could use a macro. This macro assumes that you have five columns of data, with

Status What Jan Feb Mar

in A2:E2 as your headers, and that your data doesn't extend more than 10,000 rows, with
miscellaneous stuff mixed in. The basis is "Needed" in column A. (We could write it to take the
exact number of rows into account, but I'm lazy.... ;-))

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim mySht As Worksheet
Dim myPTS As Worksheet

Set mySht = ActiveSheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Source").Delete
Set myPTS = Worksheets.Add(Befo=Sheets(1))
myPTS.Name = "Pivot Source"

With mySht.Range("A2:E10000")
.AutoFilter Field:=1, Criteria1:="Needed"
.SpecialCells(xlCellTypeVisible).Copy myPTS.Range("A2")
.AutoFilter
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & myPTS.Name & "'!R2C1:R10000C5").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status").CurrentPage = _
"Needed"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("What")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jan"), "Sum of Jan", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Feb"), "Sum of Feb", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Mar"), "Sum of Mar", xlSum
Range("B3").Select

With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
End Sub



"xrbbaker" wrote in message
...
Bernie,

Your Filter suggestion had me thinking the same way. Problem is the data
isn't in perfect cube-like pivot format, and can't get that way. It has
plenty of row gaps and column gaps. It has values mixed in that are really
subsection headers. I don't know how many rows there will be. There will be
a lot of managerial interraction with the worksheet as teams of people play
"what if?" so it needs to keep the labels, gaps, subheaders, etc. As they
are playing 'what if we replace this real person with a "needed"
placeholder', and vice-versa, they will want to see the results of their
what-iffing then and there. No time for them to make a change, then hand it
over to me to cut and paste together a pivot representation. There could
possibly be hundreds of changes as they try to optimize the staffing across
projects.

I think the only way this can be done, given the real-world context, is to
(roughly)

1) loop down column A and collect the rows that contain "needed."
2) go to each row collected and read the value of column B. Build an array
containing each unique value.
3) for every occurence of each unique value, sum the person-time needs for
each month
4) spit out the results of needed skill and total person-time per month for
that skill

Sounds hairy, but I'm sure it can be done. It's just beyond me.



"Bernie Deitrick" wrote:

Russ,

Then a pivot table will work if you just want the summary: I tried it on your data set (including
blank rows between) and it worked fine. Create a header row in the top row, select all the data
(including the header row), then use Data / Pivot Table, and click through. Drag "Type" to the
Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then drag the data button to
the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I don't
know how many resources will be needed for each one. IN the end, I expect
there may be a hundred or more needs and those need types should be rolled up
into one entry. I need to show 1 line of C++ and the fact that there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows of .75
people. Maybe if the filter can do some kind of grouping or totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Ed,

Thanks for the sharp code. I was thinking that if I could get things to a
sub-set table that I'd use your code. (see my reply to Bernie) Hower, my
code actually worked and then it occurred to me that I was looking at a
perfect pivot table - the thing Bernie has been trying to drive into my thick
head!

So far I just did a cut/paste to create the pivot table. I have to play more
with it to see what's next. Like I said to Bernie I very well may end up
going down his code path as it is more elegant. Less interim steps seems
typcially to mean less problems.

Thanks very much

" wrote:

You can perform a basic formulaic sum if statement to aggregate the
months.
I've placed an example @ http://www.HelpExcel.com/Ether
It's named sumif.xls

Regards,
Eddie
http://www.ExcelHelp.us

Use SUMPRODUCT, along the lines of

=SUMPRODUCT(($A$1:$A$1000="Needed")*($B$1:$B$1000= "Java")*C$1:C$1000)

Put the Java. C++, etc down column H, and then use

=SUMPRODUCT(($A$1:$A$1000="Needed")*($B$1:$B$1000= $H2)*C$1:C$1000)

and you can copy the formula into a table, which will sum columns C, D, and
E.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Yeah Bernie, you are lazy and I'm a VBA expert.

I've been getting closer by trying to create an interim table. I THINK I
can get things into a sub-set table by using the MATCH, INDEX and INDIRECT
functions. I use match to find the first occurence of "Needed", then keep
track of the found row in another cell, and use that row +1 as the
beginning
of the next range, using INDIRECT to build the range and INDEX to grab the
values. If I can make that "ungrouped" sub-set table, then I could use
Ed's
summing code to group/total it into one line.

It was actually my trying to explain the issue to you that helped me
envision this approach/attempt. Problem is it is all brute force and ugly
as
heck. I'm going to try to finish it off and see what it looks like, then
try
to digest your code. Yours would be much more cool. Once in a pivot
table
it would be pretty easy to stand it on its head. This will keep me busy
for
days. Thanks very much.



"Bernie Deitrick" wrote:

You could use a macro. This macro assumes that you have five columns of
data, with

Status What Jan Feb Mar

in A2:E2 as your headers, and that your data doesn't extend more than
10,000 rows, with
miscellaneous stuff mixed in. The basis is "Needed" in column A. (We
could write it to take the
exact number of rows into account, but I'm lazy.... ;-))

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim mySht As Worksheet
Dim myPTS As Worksheet

Set mySht = ActiveSheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Source").Delete
Set myPTS = Worksheets.Add(Befo=Sheets(1))
myPTS.Name = "Pivot Source"

With mySht.Range("A2:E10000")
.AutoFilter Field:=1, Criteria1:="Needed"
.SpecialCells(xlCellTypeVisible).Copy myPTS.Range("A2")
.AutoFilter
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & myPTS.Name & "'!R2C1:R10000C5").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status").CurrentPage
= _
"Needed"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("What")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jan"), "Sum of Jan", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Feb"), "Sum of Feb", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Mar"), "Sum of Mar", xlSum
Range("B3").Select

With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
End Sub



"xrbbaker" wrote in message
...
Bernie,

Your Filter suggestion had me thinking the same way. Problem is the
data
isn't in perfect cube-like pivot format, and can't get that way. It
has
plenty of row gaps and column gaps. It has values mixed in that are
really
subsection headers. I don't know how many rows there will be. There
will be
a lot of managerial interraction with the worksheet as teams of people
play
"what if?" so it needs to keep the labels, gaps, subheaders, etc. As
they
are playing 'what if we replace this real person with a "needed"
placeholder', and vice-versa, they will want to see the results of
their
what-iffing then and there. No time for them to make a change, then
hand it
over to me to cut and paste together a pivot representation. There
could
possibly be hundreds of changes as they try to optimize the staffing
across
projects.

I think the only way this can be done, given the real-world context, is
to
(roughly)

1) loop down column A and collect the rows that contain "needed."
2) go to each row collected and read the value of column B. Build an
array
containing each unique value.
3) for every occurence of each unique value, sum the person-time needs
for
each month
4) spit out the results of needed skill and total person-time per
month for
that skill

Sounds hairy, but I'm sure it can be done. It's just beyond me.



"Bernie Deitrick" wrote:

Russ,

Then a pivot table will work if you just want the summary: I tried it
on your data set (including
blank rows between) and it worked fine. Create a header row in the
top row, select all the data
(including the header row), then use Data / Pivot Table, and click
through. Drag "Type" to the
Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then
drag the data button to
the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I
don't
know how many resources will be needed for each one. IN the end, I
expect
there may be a hundred or more needs and those need types should be
rolled up
into one entry. I need to show 1 line of C++ and the fact that
there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows
of .75
people. Maybe if the filter can do some kind of grouping or
totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just
the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display
what
resources are needed,by month. Through a simple VLOOKUP or by
using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part
of it, but
that's about it. That is, walk down column A until it finds
"Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I
can do some
basic VBA stuff by myself, but not this. Can anybody point me to
a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

for some reason yesterday I had trouble responding to this posting. there
was something wrong at my companies server in processing Javva scripts.
today its fixed.


If you want tto try VBA code I found this code in the VBA help. i've used
it before and it works pretty well. if you need any help let me know. This
will do the searching and identify the rows you are look for.


This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

"xrbbaker" wrote:

I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Joel,

Thanks much. Now I have several approaches to work with.

***Regarding the problem you experienced yesterday. Was it Error on page?
That's what I had and I solved it by changing the browser settings:
Internet/Options/Advanced/Browsing - uncheck Use Smooth Scrolling.

Give that a try.





"Joel" wrote:

for some reason yesterday I had trouble responding to this posting. there
was something wrong at my companies server in processing Javva scripts.
today its fixed.


If you want tto try VBA code I found this code in the VBA help. i've used
it before and it works pretty well. if you need any help let me know. This
will do the searching and identify the rows you are look for.


This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

"xrbbaker" wrote:

I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

It was pagge not found. the Java had problems loging into microsoft server.
We had a worm problem at work that may have effected the Java.

"xrbbaker" wrote:

Joel,

Thanks much. Now I have several approaches to work with.

***Regarding the problem you experienced yesterday. Was it Error on page?
That's what I had and I solved it by changing the browser settings:
Internet/Options/Advanced/Browsing - uncheck Use Smooth Scrolling.

Give that a try.





"Joel" wrote:

for some reason yesterday I had trouble responding to this posting. there
was something wrong at my companies server in processing Javva scripts.
today its fixed.


If you want tto try VBA code I found this code in the VBA help. i've used
it before and it works pretty well. if you need any help let me know. This
will do the searching and identify the rows you are look for.


This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

"xrbbaker" wrote:

I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5

Try using a combination of INDEX, and MATCH. These are much more flexible
than vlookup and hlookup and can accomplish almost any table lookup function.
Remember to have a value for all rows or columns for your match lookup
range- they can repeat if they are dummy values.
"xrbbaker" wrote:

I'm trying to do some resource forcasting and in the end display what
resources are needed,by month. Through a simple VLOOKUP or by using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part of it, but
that's about it. That is, walk down column A until it finds "Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I can do some
basic VBA stuff by myself, but not this. Can anybody point me to a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5