PDA

View Full Version : Limit data field in PIVOT TABLE



anandbohra
08-18-2008, 02:45 AM
Hi friends,

pl suggest me if there is any way to limit the data field value in pivot table.

my table structure is as follows
nse code in row area & holder in column area & data area consists of closing stock qty.

now I want to limit the closing stock qty to show values above 10 share onwards (means do not show nse code where closing stock is less than 10 shares )

pl help & suggest whether it is possible or not

marshybid
08-18-2008, 03:21 AM
Hi there,

If you provide an example spreadsheet I will have a look at it for you.

Marshybid

anandbohra
08-18-2008, 10:45 PM
hi thanks for the reply
pl see the attached file here highlighted fields I do not want to see
coz my criteria is do not show the scrips in PIVOT where data value is less than 11.

for this I got alternate idea to use this code which first delete the values then generate the pivot


Sub delete_shares_matching_criteria()
Application.ScreenUpdating = False
Dim iLastRow As Long
Dim i As Long
Dim p
p = Sheet1.Range("B2").Value
With Sheet2
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, "C").Value < 0 Or .Cells(i, "C").Value <= p Then
.Cells(i, "C").EntireRow.Delete
End If
Next i

End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub

now user can put say 11 & run this code so this code will delete all the rows where closing quantity for scrips is less than given value

but the drawback is it is not considering cumulative say I have 80 shares of MIC EQ for Mr E (u can see in attached file)
but the row data consists the same in 5 rows & in 2 row it matches the criteria & hence gets deleted so I want to avoid the same

& thus wants pivot to judge after making report to delete the records where qty is less than specified criteria
MIC EQ