Empty rows after grouping

[Guest]

I am creating a spreadsheet links to a Project Web Server timesheet cube,
and formats that data so that it can be pulled into Office Accounting and
other applications. I am using a pivot table, and almost have it formated
the way I want. I have one problem to solve.

Each employee has multiple projects. The employee is listed on one row in
column A and his projects are listed on multiple rows in column B. This
leaves empty fields where I need the employee name listed again:

Names Projects
1stname 1st Project
2nd Project
2ndname 1st Project
2ndProject

Does anyone know how I can get the names to show up for all rows?

Thanks,

 

[Guest]

Is this what you want to achieve?

Names Projects
1stname 1st Project
1stname 2nd Project
2ndname 1st Project
2ndname 2ndProject

If yes. You cannot use the pivot to get this directly. But instead, create
the pivot table first, then copy it to somewhere and paste data. Then you
need a formula likse liks by inserting a col in the middle to "fill-down" get
the missing part.

Start with the 2nd cell (b2), the first cell you need to copy from A1
=IF(A2="",B1,A2)

Then copy and paste down then you will get the result.

then copy and paste value for this middle col. Then finally delte the first
col. let's make it Col "B".

 

[Gord Dibben]

Ken

Select the Names column and F5>Special>Blanks>OK

In the active cell enter an = sign then point or arrow to cell above then hit
CTRL + ENTER.

Copy>Paste Special(in place)>Values>OK>Esc.


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

When I try your solution I get window that says "You cannot move a part of a
Pivot Table report, or insert..." Any ideas?

Thanks,

Ken

 

[Guest]

Hi Leung,

Unfortunately the number of projects change making the cells different each
time we use the report.

Thanks,

Ken

 

[Gord Dibben]

Ken

My mistake.......did not read "all" your post..........missed the part about
Pivot Table.

My solution was for a regular column of data.

I don't work enough with PT to be able to offer any assistance.

Gord