Forum Discussion
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 repeat for that month only and not all the months in the table.
Please see my example spreadsheet with the desired output, which should make it clear.
I just need a formula to make this output table. I guess LET would be possible, but can anyone help please?
Thank you
Matt
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...
12 Replies
Normally a simple cross join using TOCOL-IF would suffice, but the additional criteria to repeat only some records complicates things:
=LET( rng, TRIMRANGE(A2:R14, 2, 0), arr, IF(ISBLANK(rng),"",rng), hdr, TAKE(arr, 1), bdy, DROP(arr, 1), rId, SEQUENCE(ROWS(bdy)), dts, TOROW(dates, 1), inc, ISNUMBER(XMATCH(CHOOSECOLS(bdy, 4), dts)), key, FILTER(rId, (CHOOSECOLS(bdy, 1) <> "") * NOT(inc)), one, TOCOL(IF(dts, key),, 1), two, FILTER(rId, inc), top, CHOOSEROWS(bdy, one), all, VSTACK(HSTACK(TAKE(top,, 3), TOCOL(IF(key, dts),, 1), DROP(top,, 4)), CHOOSEROWS(bdy, two)), VSTACK(hdr, SORTBY(all, CHOOSECOLS(all, 4), 1, VSTACK(one, two), 1)) )It's a little messy but works with your sample file. Please note, it will return #CALC! if none of the records contain a matching date, or if all of the records contain matching dates (I did not generalize it to accommodate these possibilities).
Hi
Thanks for sharing this.
However when copying this into my test sheet attached here, I get the #NAME error. Looks like the range and dates is correct. Any ideas?
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
Maybe, though I can't think of a formula solution right now. And I prefer not to use formulas that most likely get quite complex when there is an easy PQ solution staring at me :)
Perhaps the attached PQ solution is something for you. I didn't make any effort to make it fancy but it seems to do what you asked for.
Thank you for this. However ideally I need in the way of a formula using LET or FILTER etc
Is this an option please?
Your output table is empty and do not make sense what you want. Can you please fill-up your output table with desired output so that we can understand what you want?
Hi Harun, looks fine to me. The output table is below the input table.
Have uploaded again here
