Forum Discussion
Solved
Repeat list of items for each month in table
Hi all I am looking to have an automatic repeating list of items that repeats based on the amount of months in a table. If however a month is mentioned in the list of items, the item will only rep...
My best guess would be the TRIMRANGE function is not yet available to you. I'm on the Current Channel for MS365 Business and just got it this month (last week).
Try the same formula but change the rng variable to just A2:R14 and it should work...
As variant
=LET(
noDates, FILTER(Table1, Table1[Month] = ""),
n, ROWS(noDates),
m, COLUMNS(noDates),
combine, VSTACK(
REDUCE(Table1[#Headers], dates,
LAMBDA(t,date, VSTACK(t, LET(
RepeatDate, IF(SEQUENCE(n), date ),
AddDate, HSTACK(noDates, RepeatDate),
CHOOSECOLS(AddDate, HSTACK({1,2,3}, m+1, SEQUENCE(,m-4,5)) ) ) ) ) ),
FILTER(Table1, Table1[Month] <> "") ),
SortByDate, SORT(combine,4),
PutEmpty, IF(SortByDate="", "", SortByDate),
VSTACK( TAKE( PutEmpty, -1), DROP(PutEmpty, -1) )
)I practically literally repeated Riny_van_Eekelen query. In above perhaps better to add header after sorting using DROP(REDUCE,"",,,),1), didn't re-write.
This looks like it would work but how would it work in data not in a table? I want to use named ranges only. When I tried adjusting your formula, I got the #VALUE error