How do I repeat first column in Excel pivot table?

easybill
easybill used Ask the Experts™
on
See attached spreadsheet.  I created a Pivot Table based on the "rawdata" worksheet.  It's what I want but for formatting purposes I want the first column to repeat for each row.  Is that possible?
2007-jun06-active-items-combined.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
In a Excel 2003 or earlier PT, you can't.
Top Expert 2010

Commented:
1peterx,You cannot do that in a PIVOT TABLE.:)Patrick

Commented:
I haven't tried your file cause its 3.9MB. Perhaps, you could compress it and load it again or may be reduce the data and load it.

By that time, you can try this tweak.
Copy the Column which u want to repeat and insert it at the same place. And then, refresh your Pivot table and in the field list you will see that column twice and so, you can add it again.


Tils
Top Expert 2010

Commented:
BTW, Excel 2010 added the capability of repeating the row labels: http://blogs.msdn.com/b/excel/archive/2009/10/15/a-few-more-pivottable-improvements-in-excel-2010.aspx.  See the section for "Repeat Down Labels".I knew that was a recent addition, but I couldn't remember if it was introduced in 2007 or 2010.Of course, this means that the correct answer, with respect to PTs, is "you can't", if you are on version 2007 or earlier.

Author

Commented:
I'm currently using Excel 2002 and unfortunately stuck there for now.

Author

Commented:
1peterx,

I can convert the Pivot Table data to a regular spreadsheet and try that.  Stand by.

Commented:
Opps, i mistook the question!

Author

Commented:
This is just a one-time task.  Does anyone have Excel 2010 that could do this for me?
Most Valuable Expert 2011
Top Expert 2011
Commented:
Just copy the table, then paste special-> Values. Now select the first column, press f5, click the Special button and choose Blanks. Now type = then press the up arrow and then press Ctrl+Enter.


Commented:
May be you can try this code.
It does it for the Column A
Sub FillGapsColumnA()

Dim i, irow As Integer
irow = Range("B65536").End(xlUp).Row

For i = 3 to irow
    If Range("A" & i) = "" Then Range("A" & i) = Range("A" & i - 1)
Next

End Sub

Open in new window

Top Expert 2010

Commented:
No need for Excel 2010, if all you want to do is create a text matrix.1) Put this formula in K5...=IF(A5<>"",A5,K4)2) Copy that down and across through M3626

Author

Commented:
rorya:   worked!  awesome!  Thanks!

Author

Commented:
Piece 'o cake, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial