Seek input on a Transposition: Can Excel help do this?

[gimme_this_gimme_that]

I have data that goes

Name,Project,Period,Count

A1, B1, 5,10
A1, B1, 6,20
A1,B2,5,5
A,B1,5,20
A2,B2,5,10
A2,B2,6,100
A3
A4,B3,5,200

And I want it to look like (filtering out the A3)

Name Project Period5 Period6
A1 , B1, 10, 20
A1, B2, 5
A2, B1, 20
A2, B2, 10, 100
A4, B3, 5, 200

Or to do this do I have to write the VBA and work on all the looping?

Thanks.

 

[gimme_this_gimme_that]

Hi Folks,

I just about have things going when I use an Excel Pivot table

The problem is that I get a count of periods when I really want a total
of Counts.

So I'm getting

Name Project Period5 Period6
A1 , B1, 1, 1
A1, B2, 1, 1
A2, B1, 1,
A2, B2, 1, 1
A4, B3, 1, 1

Is there a way to tell the pivot table to use Periods when it goes
across but to add Counts instead of periods in the contents of the
cells?

Thanks.

 

[Guest]

Use a Pivot Table... Place your cursor in the middle of your data set. Select
Data -> Pivot Table. Now either follow the Wizard or just select finish (you
can probably get away with finish as the defualts are normally good enough.
Drag the name and project to the left hand column and the period across the
top. Put the counts in the middle and you are good to go...

 

[Tom Ogilvy]

right click on the cell with the Period 5 entry and select Field Settings
(toward the bottom of the popup). Change the selection from count to sum.

Repeat for period6

 

[gimme_this_gimme_that]

Thanks Tom.

Is there a way to filter out rows having nulls, like the row with A3?

 

[Guest]

I don't believe there is for the specific situation you describe. However,
you could copy you data source to another sheet, delete those rows (select
column 2, do edit=>goto=>specialCells, select blanks, then edit=>Delete) and
use that as the source for your pivot table.