John Jairo V
Well-Known Member
Ok vletm... you're right. I didn't see it. Blessings!
Sorry to bother you again...I am also grateful for your help...I know I did not mention it earlier...but is there any way that I can also get the the sum of values in particular month on eom date of that month...I mean like in pic below please see into it.. values in bold are eom dates and the sum of the values in that particular monthIts working!!!!!! My lord thankyou very very very much...You just saved me from disaster.....thankyou thankyou thankyou.....
= LET(
start, EOMONTH(MIN(InputDates),0),
end, EOMONTH(MAX(InputDates),0),
m, 1+DATEDIF(start, end, "m"),
n, COUNT(InputDates),
k, SEQUENCE(m,,0),
i, SEQUENCE(m+n),
monthStarts, EOMONTH(start, k-1),
monthEnds, EOMONTH(start, k),
monthlyTotals, SUMIFS(InputValues, InputDates, ">="&monthStarts, InputDates, "<="&monthEnds),
appendedDescr, IF(i<=n, "Entry", "Subtotal"),
appendedDates, IF(i<=n, INDEX(InputDates, i), INDEX(monthEnds, i-n)),
appendedValues, IF(i<=n, INDEX(InputValues, i), INDEX(monthlyTotals, i-n)),
dates, UNIQUE(SORT(appended)),
combined, CHOOSE({1,2,3}, appendedDescr, appendedDates, appendedValues),
SORT(UNIQUE(combined),2) )
I am already grateful to you for helping me in this...This sheet is perfect.....but my problem is to get the sum of values in the same column of values as I depicted in #27 and if possible please also add the difference of days between two adjacent dates in the column next to values...after this I won't ask any modifications in this thread...I spent whole day yesterday trying to modify with no luck....but as I already said I am already grateful to you for all the helpmahesh402
Were You over 50% sure that there won't be any values ever in the end of month dates?
As well as You would have unknown number of rows of data every time ...
My sample version ...
every time I entered the this code with control+shift+enter and replacing the input dates with date range and input value with value range...but it shows error could you please tell how to apply above code in ms excel 2007 . ....but yes I want exactly this output.... thanks once again for giving your precious time to solve my problemThe formula is getting long!
View attachment 72735Code:= LET( start, EOMONTH(MIN(InputDates),0), end, EOMONTH(MAX(InputDates),0), m, 1+DATEDIF(start, end, "m"), n, COUNT(InputDates), k, SEQUENCE(m,,0), i, SEQUENCE(m+n), monthStarts, EOMONTH(start, k-1), monthEnds, EOMONTH(start, k), monthlyTotals, SUMIFS(InputValues, InputDates, ">="&monthStarts, InputDates, "<="&monthEnds), appendedDescr, IF(i<=n, "Entry", "Subtotal"), appendedDates, IF(i<=n, INDEX(InputDates, i), INDEX(monthEnds, i-n)), appendedValues, IF(i<=n, INDEX(InputValues, i), INDEX(monthlyTotals, i-n)), dates, UNIQUE(SORT(appended)), combined, CHOOSE({1,2,3}, appendedDescr, appendedDates, appendedValues), SORT(UNIQUE(combined),2) )
sorry for not answering previously...I read your question with all my attention but I have too many things going on so I just forgot to mention..so yes there may be chance in one or two cases out of around 65 cases in hand where I have values on the eom but if there are values then I requiremahesh402
Did You answer to my question? ... or notice it at all?
Were You over 50% sure that there won't be any values ever in the end of month dates?
eg if You have value 111 already in 28-Feb-2018 ( yellow cells )
Which value would You want to have in 28-Feb-2018 ... 111 or 211?
How would You know that there were something before?
View attachment 72744
... difference of days ... means like above (with extra sample end of month 28-Feb-2018 -value)
your version is great.....superb and excellent......but just a little thing is that the no of days after pressing [Do it] button in column C are not proper after 7 th row but the expected outcome of days should be like in column D...plz see picmahesh402
Your: ... I ( mahesh402 ) spent whole day yesterday trying to ... all my attention but I have too many things going on so I just forgot to mention ...
And with clear answer, You would have time to do ...
I did my version, because there seems to be still some missing rules.
You could see some of those from this file.
... eg difference of days based input
... and if Your activecell is C1 before press [ Do It ] , then there would be extra values to show.
... if You will press more that once [ Do It ] ...
Yes sir you are completely right but please see #33 ( I requested that sum should be in same column of values...but days column was right in that reply(#33).....)I mean I do not require the days between the original dates but after applying eom dates..this is what I tried to show in column D(#36) and this is exactly in the reply #33(only days column part)...Thanks for help againmahesh402
What would those C-column numbers show?
Differences of days between between values OR whatever dates?
View attachment 72773eg 91 ... is differences from 15-Apr-2018 to 15-Jun-2018 based Your given values.
You are right!!!!! but the data I have in hand is not that simple moreover I need this code because the result from this codes will becomes input for the further process... I mean I know the further process once I got the code as per request.....I could have shared the data but due to some office restriction I am unable to share otherwise you guys are awesome has already solved the 95% of my problem and I am thankful to you already...Sorry, maybe it's still too early in the new year for my head to tackle Excel issues, but why all this complication while a simple pivot seems to answer the need?
View attachment 72776