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...

Example.xlsx12 KB

excel

Formulas and Functions

djclements
Feb 11, 2025
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...
Example_2.xlsx17 KB

djclements

Silver Contributor

Feb 10, 2025

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).

matt0020190

Brass Contributor

Feb 11, 2025

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?

Forum Discussion

Repeat list of items for each month in table