Can a PivotTable separate 20% of a total?

[Iceman1988]

Hello

My question relates pivot table since i'm trying to make a pivot select 20% of a total of values and i can't see how i can do this

I've searched for topics like this throughout the web but i was unable to find the help i needed...

So i came to the place which every thing about excel is made possible

I've posted the attachment file with the columns i wanted to do this

i want for it to select 20% of the count of the column B:B

Pivot Table test.zip

Thanks in advance
G.Martins

[newdoverman]

Could this be what you are looking for? I inserted a Calculated field and grouped the date by year, month and day.

[Iceman1988]

Hi and thank you for replying

it's not quite what i was looking for but to be more specific imagine the following

I have thist pivot table:

CCO COUNT OF DAY 01-05-2013
15 14
16 23
17 11
18 15
21 16
34 14
49 12
53 30
55 13
62 12
66 15
84 17
89 19
96 12

And i want to be able to seperate like 10 random records of each "CCO"

Can i acomplish this with a pivot table?
Or do i need another method?

Thank you so much for the help
G.Martins

[newdoverman]

A Pivot Table, probably isn't the best way to get the random results that you want. The place to extract random samples would be on sheet1. The problem with using RAND() is that it is a volatile function that changes the results with each action that you make on the sheet. I have seen this kind of thing done with VBA but I am not familiar enough with it to accomplish what you want.

The way I understand your problem is that you want to have 10 samples of the data taken at random for each day from the CCO column. The procedure would probably follow something like: Assign a random number to all the samples for the day, copy those results and paste back only the values so that the formulae would no longer be there and the results would remain static so that you can work with them, then 10 of the resulting samples would be identified and extracted to another column.

Perhaps a VBA expert can take it from here.

[Iceman1988]

Thanks for your reply

Since this will be complicated without using VBA can you remember where you've seen this done in VBA? It might not be the solution but it would be a great help if i at least had something to dig in and try to adapt

Thanks in advance
G.Martins

[newdoverman]

It was a private workbook done to study how working hours were spent by a group of employees who had similar duties. I doubt that it still exists and being retired wouldn't have access to it anyway.

[Iceman1988]

humm...

In that case all i can do is wait and see if someone as a solution in VBA for this case

thanky for all your help Newdoverman