Posts by FaKest

    Here is the answer for my question.


    And it is possible to use a excel a formula as well.

    Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six existing months:


    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    If you might have more than 12 months in the Pivot Table, then you need to check for the year also:

    Code
    1. =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0})),"Years",YEAR(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    Correcting myself

    The rule for the semester would be:

    The semester start in the month we are now (like April) and goes back 6 months. Apr, Mar,Feb,Jan,Dec,Nov (it would be the months of the last semester and from this months take the Active months in the pivot) and The active month would be Mar,Feb, Jan 2020 & Dez 2019

    Hello,

    I need a code that will count how many months was active in the past semester.

    I have a pivot and the data changes all the time when the pivot is refreshed, However, I need to calculate the last 6 months average but only using the months that was active in the last semester. e.g
    2020 2019
    Jan May
    Feb Jun

    Mar Jul

    Aug

    Sep

    Oct

    Dec


    in this case I would take the average for the last 6 months only using 5 months (2020 Jan, Feb, Mar & 2019 Dec, Oct) because it is the months there person was active in the last semester).

    I have this code for the last 6 months in a row



    But I am guessing I need to use IF the last 6 months is = 6 active months in a row then go to SUB Sub Last_6_Months_Average()
    else
    But IF the last 6 months is < 6 active months in a row Then count the number of active months in the last semester and select the total amount of this active months and do the average of the values.
    cells(number of active months, 3).value ="Average"


    Many someone can help me with this code?

    Hi all,

    Not sure how to go about these formulas I'm working on with the data I have been given.


    I have this formula in all my C column and when I don't get a correct date result it instead to be blank will show the date 1/0/1900. The problem is that I am using this column C to create a pivot and the column C is the Pivot's date, however I need to get rid of this year 1900


    =IF(COUNTIF($B$1:B2,B2)=1,A2,0)


           any ideas how I could get rid of it or maybe replace for the word Blank?

    Hello,

    The excel user will export the data from an online website to excel (12 months data), so the data will be all the time different. So the formula would need to be dynamic.


    Want to calculate the past 6 months average (However, the calculation need to use the months I have in the data, and sometimes there will be less than 6 or 12 months, or it may be like Jun, Aug, Sep, Dec, -2019 & Feb, Mar, Apr 2020), but I still need to get the average and frequency for it. I am trying different way with this formula



    =IF(MONTH(MAX('12 Months'!A:A)-MIN('12 Months'!A:A))>=6,COUNTIF('12 Months'!A:A,">="&EDATE(MAX('12 Months'!A:A),-6))/6,AVERAGEIF('12 Months'!F:F,">="&MIN('12 Months'!F:F))/MONTH(MAX('12 Months'!F:F)-MIN('12 Months'!F:F))) ''' this well, I simple dont know. :D


    and


    =AVERAGEIF(('12 Months'!A:A),">="&EDATE(MAX('12 Months'!A:A),-12),('12 Months'!F:F)) ''' this calculates as full calendar year and not only 6 months on my data.


    However it doe snot give me the correct Data.



    Ps: I post a similar question on https://stackoverflow.com/ques…st-12-and-6-month-average

    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.


    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

    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

    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?

    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

    You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.


    https://stackoverflow.com/ques…-error-in-the-same-workbo


    I just reread the rules ( The forum Etiquette* ) , and I understand what do you mean (to be honest with you, first I thought it was only about the same question here in this forum, but now I understood).

    I apologize; it will not happen again.

    Thank you for your time.


    Re: "would it be possible to use something else on the place of the merge cells?"

    Use "CenterAcrossSelection" instead.

    BTW, how are the pictures put onto the Sheet?

    If that is done with code, you can name them as they are put onto the sheet.

    I use a code to put the pictures on the sheet.


    You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.


    https://stackoverflow.com/ques…-error-in-the-same-workbo

    Yes I did, and I am very thankful for all the help.

    I apologize I will reread the rules.

    It may appear silly, but I did not notice it was the same website or linked in some way.

    I will read the rules once more to make sure it will not happen again.

    I am sorry and thank you for all your help.

    Jolivanes,

    The code is working perfectly fine, and it deletes the pics inside the merge cells. However, I am curious, would it be possible to use something else on the place of the merge cells?

    Thank you.

    The code in Post #9 just replaces this

    Code
    1. Set xRg1 = Range("B75:K136")

    So you should end up with something like this.