• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table age and gender.

Belleke

Well-Known Member
Hi,
I have a sheet Data with a table.
In the second sheet I need A pivot table with age and gender.
See example.

Thanks.
 

Attachments

  • Chandoo pivot t..xlsx
    12.8 KB · Views: 6
Belleke
sheet Participant list (Dutch)
... seems to wrote/saved ... hmm?
... with which font or
is it empty?

So, I did pivot from 'Data'-sheet ...
 

Attachments

  • Chandoo pivot t..xlsx
    15.6 KB · Views: 6
Hi vletm,
Thanks for the reply.
I forgot to ask.
Can you include the dates.
So that I can make monthly lists
 
Dates should come from kolomn b.
Dan I can select a month and i get the list with the Participants of selected month.
It is the first time I work with pivottables and I have problems to implement in the real sheet.
 
Dates should come from kolomn b.
... do You mean from these four cells?
Screenshot 2019-01-24 at 13.21.05.png
... and what kind of output would You like to get?

Seems that You have some challenges ... hmm?

How do you get these?
I did Pivot-table as You can see from file...
... after that I used 'Group'-option from Pivot-table to create same as Your file.
>> I have only ages 15 & 36 as You can see above and from file.
 
I repost my example because I forgot something very important.
In column 13 there are activities
I am looking for a pivottable so i can make monthly reports with the number of females and males,there ages and the activity.
Thank you for your time and effords because I am getting nowhere.
The pivottable of Grah + the possibility to filter on the activities from column 13
 

Attachments

  • Chandoo pivot t.(2).xlsx
    23.9 KB · Views: 1
Hi @Belleke,

Is the table your "raw" data, or is it already processed data?
I'm a bit in the dark here how it is supposed to work (process) and how the result should be like.
You can add "activities" in the PQ. See upload (still rough)
 

Attachments

  • Copy of Chandoo pivot t.(2).xlsx
    24.2 KB · Views: 4
Last edited:
Hi Guido,
Thank you.
We are almost there.
That's the table that I was looking for.
I have a few questions.
See the red text in the picture.
Also, how did you make it. Is this a pivot table because I can't find options to change the source.
I have 0 experience with pivot tables.
Thanks in advance.
 

Attachments

  • chandoo.png
    chandoo.png
    13.9 KB · Views: 8
Hi again,

It is a Power Query solution, not a direct Pivot Table. Just wanted to show that you can have all "bins" in the result. If you don't want them in the result, they can be filtered out.
 

Attachments

  • Copy of Chandoo pivot t.(3).xlsx
    29.4 KB · Views: 3
Hi Guido,
Thanks for your help, this is exactly the table that I need.
There is only one thing, I can't change the source table, and I don't know how to change the query's.
Can you advise me how to do it?
Thanks
 
You're welcome Belleke, and you should be able to update the table now.
Look for the query via the ribbon "Data -> Show Queries" (for Excel 2016). This opens the query pane, then select the one with forPivot in the name, right click and edit. Go through the applied steps at your right. It should make some sense. Don't hesitate to ask for more help.

As it turns out, you can make the same pivot without PQ. Use a tabular lay-out, drag the fields "Bins" (a lookup to the helper table), "date" (which you'd group in months),"activities", and count the "Nr".
 

Attachments

  • Copy of Chandoo pivot t.(3).xlsx
    37.3 KB · Views: 0
Don't hesitate to ask for more help.
I have to because I am getting nowhere. somehow I can't change the source.
Here is the real example.
Thanks Guido
 

Attachments

  • Registratieformulier.xlsm
    176.7 KB · Views: 1
Should work like this.
Because your titles are in Dutch and before in English, PQ got into trouble. So I changed column titles in the M-code.
 

Attachments

  • Copy of Registratieformulier.xlsm
    146 KB · Views: 5
Hi Guido,
There is something wrong with the table when I renew.
Can you have a look at it?
Thanks in advance
 

Attachments

  • Registratieformulier(v2).xlsm
    182.7 KB · Views: 4
I think the results afther renew are not correct.
In the data sheet I have
Docent Nederlands (2x)
Nederlands voor beginners
Praatgroep Frans
Nederlands gevorderden Polaroidgebouw
Naailes, schilderles, Frans
and in the report I get
Praatgroep Frans
Totaal
Docent Nederlands (1X)
Totaal
Nederlands gevorderden Polaroidgebouw
Totaal
 
But for different months, so the counter is always 1, or do I start needing glasses? There a 6 rows in the data, and the pivot table shows 6 in grand total, no correct?
 
Back
Top