1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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.

Discussion in 'Ask an Excel Question' started by Belleke, Jan 24, 2019.

  1. Belleke

    Belleke Active Member

    Messages:
    549
    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.

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Belleke
    sheet Participant list (Dutch)
    ... seems to wrote/saved ... hmm?
    ... with which font or
    is it empty?

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

    Attached Files:

    Belleke likes this.
  3. Belleke

    Belleke Active Member

    Messages:
    549
    Hi vletm,
    Thanks for the reply.
    I forgot to ask.
    Can you include the dates.
    So that I can make monthly lists
  4. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Belleke
    Many things are possible ...
    but ...
    Which dates?
    Eg tThere are totally two birthday-dates.
  5. Belleke

    Belleke Active Member

    Messages:
    549
    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.
  6. Belleke

    Belleke Active Member

    Messages:
    549
    How do you get these?
    Code (vb):
    <6 or (blank)      
    6-15      
    26-35      
     
    I have
    Code (vb):
    15      
    35  
    42  
    53      
     
  7. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.
  8. Belleke

    Belleke Active Member

    Messages:
    549
    The real table has more than 450 participants
    The group option worked. Thanks.
  9. Belleke

    Belleke Active Member

    Messages:
    549
    Somebody a suggestion so that I can make monthly reports?
  10. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Belleke
    ... with those four rows it's a challenge.
    And
    You should show needed/wanted layout too.
  11. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Belleke
    ... okay
    ... Your 'wanted' Monthly report

    Attached Files:

  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    Rough PQ solution to "pivot"
    Seeing some more data would help.

    Attached Files:

    Belleke likes this.
  13. Belleke

    Belleke Active Member

    Messages:
    549
    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

    Attached Files:

  14. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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)

    Attached Files:

    Last edited: Jan 26, 2019
    Belleke likes this.
  15. Belleke

    Belleke Active Member

    Messages:
    549
    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.

    Attached Files:

  16. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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.

    Attached Files:

  17. Belleke

    Belleke Active Member

    Messages:
    549
    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
  18. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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".

    Attached Files:

    Belleke likes this.
  19. Belleke

    Belleke Active Member

    Messages:
    549
    I have to because I am getting nowhere. somehow I can't change the source.
    Here is the real example.
    Thanks Guido

    Attached Files:

  20. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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.

    Attached Files:

    Belleke likes this.
  21. Belleke

    Belleke Active Member

    Messages:
    549
    Thanks Guido.
    I learned a lot with this question and my problem is solved.
    Cheers,
    B
  22. Belleke

    Belleke Active Member

    Messages:
    549
    Hi Guido,
    There is something wrong with the table when I renew.
    Can you have a look at it?
    Thanks in advance

    Attached Files:

  23. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    And what 's wrong, Belleke? Is the sub-total in the way?
  24. Belleke

    Belleke Active Member

    Messages:
    549
    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
  25. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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?

Share This Page