VBA code to create a new table with unique names separating it by months.

    Hello all,

    The filter will not work in my case because the data I have will change all the time (sometimes it will be a lot and sometimes it may be only few names).

    The excel user will export the data from an online website to excel (12 months data).


    My question: is there any VBA code that will "automatically" create a new table showing only the unique names (compering in the 12 months data) and put it separate by months?

    e.g:

    MAY:
    Able Target Limited

    Denline Uniforms, inf
    Electron Microscopy Sciences


    Jun (will not even appear because no unique names


    Jul

    DLAB....


    This need to be dynamic, because all the export data will be different.


    This new table need to be paste in the same sheet under the name "New Beneficiaries in which month" (range ("J9")

    Thank you

    Maybe it would be more easy if I use the data on 12 months direct and try to create a new table from it or it is possible to use the pivot table with the names e date already created insude the tab Data?

    Quote from royUK

    I think you probably need to gibe a bit mote information about what you want to do.

    I want to have a table with beneficiaries names that are appearing for the first time in every month, but the names need to be separate by months as well.


    The names will be taken from data in the sheet named 12 months, and this data will be different all the time, so the table with uniques names need to be dynamic.


    I am not sure if I need to do it using the datasheet or could it be from the pivot table with the names by month.

    I put the an image showing what I mean


    I have 2 sheets. 1 with 12 months of data export from online sources and another sheet where a Pivot table was created with all the names separated months.


    I mean, I understand how to find and copy unique values to another place, but I don't know how I can get the results I need.


    I need a table with beneficiaries names that are appearing for the first time in every month, but the names need to be separate by months as well. However, each month values need to be compared with previous months and not directly/only with all 12 months data.


    I am not sure if I need to do it using the datasheet or could it be from the pivot table with the names by month.


    You can see in the image the result I need to get

    I am clueless in how to achieve this.

    I use excel 2019. Well don't really need to be from the pivot table, it can be from the 12 months data I have in 1 of the sheets (therefore, need to keep in mind the data will be different each time). I don't know how I can do it, so the new table will only show the new names compared with previous months (like the images). "No new names" is not really necessary, can be 0, empty or not even show in the table (it is only, so the user will know that in this month there are no new names).


    ps: I did post a similar thread on https://stackoverflow.com/ques…comment108479925_61320322

    Thank you, Roy. However, if you run the macro on my excel file, you will get the save pivot table you got.


    I got the answer how to do it using excel formula


    Code
    1. =IF(COUNTIF($B$1:B2,B2)=1,A2,0)
    6. =IF(COUNTIF($B$1:B2,B2)=1,B2,"")

    I still think that a VBA code would be better. Therefore, I am glad it is working.

    The formula was a solution I got from someone.


    I wanted to use only VBA code. However, I am clueless how to do it.


    In total, I would have 2 pivots with names.


    Pivot 1 with all the names separated by month created from 12 months data;

    Pivot 2 that would show unique names in each month.


    I copy two columns from the original data: Name and Data to another sheet and there I created 2 new columns using the formulas (because I don't know how to do it using VBA code) and from this new table I created the Pivot2.

    If you set up the PivotTables manually then the only VBA required would be to trfresh them.


    Ideally the source data would be formatted as an Excel table