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