-
Limit data field in PIVOT TABLE
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
Always Mark your Thread as Solved the moment u got acceptable reply (located under Thread tools)
Practice this & save time of others in thinking for unsolved thread
-
Hi there,
If you provide an example spreadsheet I will have a look at it for you.
Marshybid
-
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
[vba]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
[/vba]
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
Always Mark your Thread as Solved the moment u got acceptable reply (located under Thread tools)
Practice this & save time of others in thinking for unsolved thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules