Distribution of unique values from dynamic list

    Hi guys,


    I am new to this so apologies in advance if i make any stupid questions.


    I have a large database (around 300,000 columns) where each entry represents a person and i am trying to map the income distribution of my population (e.g., 5 people have an income of 0, 6 people have an income of 0.1, ..., 1 person has an income of 100,000) according to 2 specific criteria - if they have 0, 1, 2 or 3 kids and if they are younger (or older) than 25y old.
    One of my criterion works as a changing input and whenever i change it so will the eligible cases for me to compute the distribution.
    Since i need the distribution I need to count all cases for unique income values.


    I already have two additional columns with if functions to flag eligible cases.


    I tried recording a macro using a pivot table but it just wasn't working..


    Any ideas?


    Kindest regards,
    Mariana

    Re: Distribution of unique values from dynamic list


    I think this could be accomplished using a filter (probably need to do this part as a Macro - copy the income figures to a separate range, and then filter for unique values), and then use the COUNTIFS function (or COUNTIF if using version older than 2007) to count the number of occurrences of each unique combination of income, # of children, etc. If that doesn't make sense, if you want to post a sample of your data set I could put together an example.

    Re: Distribution of unique values from dynamic list


    Hi. Thanks! Also tried that with a macro (though simply in recording code, as i have no idea how to write it down), my problem is that the countifs function is quite heavy for 300,000 records and my excel was constantly crashing.


    How can i attach a file to the post?

    Re: Distribution of unique values from dynamic list


    Yes, you're absolutely right, sorry..
    When i try to run the macro i recorded (with Pivot Table) VBA returns the following error
    "Invalid procedure call or argument" and when i debug it, it just highlights the part of the code where the pivot table is being created


    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Table_Income", Version:= _
    xlPivotTableVersion14).CreatePivotTable TableDestination:= _
    "Backup!R9C34", TableName:="PivotTable1", DefaultVersion:= _
    xlPivotTableVersion14

    Re: Distribution of unique values from dynamic list


    Hi guys, already found a solution, just traced a stupid mistake i was making.. thanks!


    best regards,
    Mariana

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login