Excel Pivot Table - Help how do I display all labels in first column

[chrish47]

I am working on a project where I have to take large amounts of data, analyze it to find sections that are not zero, and prepare entries to correct those. I have 46K+ lines of data that rolls up into approximately 4000 lines in a pivot table. The problem is that to create the entries I need three fields + the amount. This is an example of what the Problem looks like:

PO Account Project Amount
10 12345 98765 100
12346 98765 -148
12347 98765 48

The problem is that I need the PO # (10 in the example above) to show for all rows of data so that when I copy it to a different spreadsheet to feed it into our system I don't have to manually cut and paste the PO # down for all 4000+ PO's. Any help would be greatly appreciated.

Also any way to suppress zero values in this same project would be greatly appreciated. With the same data as an example 46K lines, rolling into 4000 Pivot Table rows, only say ~856 of those rows don't sum to zero. Is there an easy way to eleminate or suppress all the rows that sum to zero so that I don't have to do that process manually?

I am familiar with VBA for Excel and Macros, but I haven't worked much with Pivot tables, if someone has a better solution using VBA I would appreciate it (or a way to do what I am asking with the raw data using VB - group based on one column (Ref#) and show three other columns (PO, Account, Project), plus the sum of the amounts. This is something that I have to do with large amounts of data on a regular basis so I would really appreciate any help. If more info is required let me know.

Thanks,
Chris

 

[xlbo]

AFAIK, you can't get the PO# to appear on all lines - but you can write some VBA to fill in the blanks

Sub fillem()
For i = 1 To Range("A65536&quot.End(xlUp).Row
If Range("A" & i).Text = "" Then
Range("A" & i).Value = Range("A" & i - 1).Text
Else
End If
Next i
End Sub

assuming data in A, starting in A1

As for zero values....don't think that's possible if it's in the data field - you can hide certain values in row / column fields but niot data fields (not in xl97 anyway)
Rgds
~Geoff~

 

[chrish47]

Thanks for the info, I thought I had done it on a computer somewhere, but I don't recall what version of Excel it was. Anyway the VB approach you suggested seems to be the best bet, I was hoping to do everything with the Pivot table, but it doesn't look like that will happen.

I took your approach, and customized it to run from the current cell down (for filling in values in the other columns). The modified code is attached if anyone is interested.

Thanks for the tip, geoff.

Modified Code to fill column with value from previous rows

Sub Fill_Label_Column_Cur()
'Macro to fill empty column values with value from previous row
'Used to fill in copied Pivot tables for Processing as JV's
'Chris Huss, 12/12/02


Dim CurCol As Integer, CurRow As Integer, i As Integer

CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
'Starts at current cell and fills empty cells in the column with value from previous row
For i = CurRow To Cells(65536, CurCol).End(xlUp).Row
If Cells(i, CurCol).Text = "" Then
Cells(i, CurCol).Value = "'" & Cells(i - 1, CurCol).Text
Else
End If
Next i
End Sub