CODEWITHSUNDEEP
excelvbapivot-table
Fah
Fah

Reputation: 203

Is there a way to create a dynamic table with unique names separating it by months?

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

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

desired result

enter image description here

ps: I did post a similar thread on https://www.ozgrid.com/forum/index.php?thread/1227259-vba-code-to-create-a-new-table-with-unique-names-separating-it-by-months/&postID=1233806#post1233806

Upvotes: 0

Views: 666

Answers (1)

E.Wiest
E.Wiest

Reputation: 5915

Another option :

Excel.Sheet

Excel.Names

Cell with orange background : standard formulas (dragging down needed)

Cells with green background : array formulas

For this example, the sheet could manage 100 records. "Orange formulas" need to be dragged down to extend this limit. Formulas used : MONTH,FILTER,INDIRECT,IF,COUNTIF,COUNT,OFFSET

Brief description :

Column A:B : name of the person + month (as date format !). You can copy paste your value to test.

Columns F:I : First we order the table. With this new table we "code" the new (1) and the already known person (0).

Columns L:O : We filter the table to keep only the new people.

Columns U:Y : We generate another table which contains the month with no new people.

Columns AC:AE : We combine the 2 previous created tables and we pivot.

Column AH : result.

I hope the mechanic would be clear enough.

Upvotes: 1

Related Questions