= LET(
start, EOMONTH(MIN(InputDates),-1),
end, EOMONTH(MAX(InputDates),0),
m, 1+DATEDIF(start, end, "m"),
n, COUNT(InputDates),
k, SEQUENCE(m,,0),
i, SEQUENCE(m+n),
monthEnds, EOMONTH(start, k),
appended, IF(i<=n, INDEX(InputDates, i), INDEX(monthEnds, i-n)),
dates, UNIQUE(SORT(appended)),
IF({1,0}, dates, XLOOKUP(dates, InputDates, InputValues, "") ) )
=LET(d,A3:A8,v,B3:B8,
em,UNIQUE(EOMONTH(+d,0)),
df,AGGREGATE(15,6,IF({1,0},d,em),SEQUENCE(COUNT(d)+COUNT(em))),
IF({1,0},df,XLOOKUP(df,d,v,"")))
yes it looked like as per the above picture but when it pressed [Do it] button it becomes like this...mahesh402
Do that sheet look as below after You've opened it?
View attachment 72706
What will 'change' after You have pressed [ Do It ]-button?
If not as above then ... save that file and send it back here.
Per Your writing... it seems that E&F-columns won't sort.
The code is in that file - You can get it from there.
>>>
mahesh402
Here other sample version without sorting by adding those missing dates.
do I have to paste above code in excel cell????Hi to all!
Another option could be:
Blessings!Code:=LET(d,A3:A8,v,B3:B8, em,UNIQUE(EOMONTH(+d,0)), df,AGGREGATE(15,6,IF({1,0},d,em),SEQUENCE(COUNT(d)+COUNT(em))), IF({1,0},df,XLOOKUP(df,d,v,"")))
Allways put your excel version when you need some help.I am using ms excel 2007...