Forum Discussion

matt0020190's avatar
matt0020190
Brass Contributor
Feb 09, 2025
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...
excel
Formulas and Functions
  • djclements's avatar
    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...

djclements's avatar
djclements
Silver Contributor
Feb 11, 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's avatar
matt0020190
Brass Contributor
Feb 11, 2025

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?

  • djclements's avatar
    djclements
    Silver Contributor
    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...

    • matt0020190's avatar
      matt0020190
      Brass Contributor
      Feb 11, 2025

      Works perfectly, thank you for your time and support.

      Obviously as the "icing on the cake" and to make the solution perfect, this would be good to resolve in case of all records matching dates and/or none:

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

      • djclements's avatar
        djclements
        Silver Contributor
        Feb 12, 2025

        A bit cleaner, with some "icing":

        =LET(
    rng, A2:R14,
    arr, IF(ISBLANK(rng),"",rng),
    hdr, TAKE(arr, 1),
    bdy, DROP(arr, 1),
    rId, SEQUENCE(ROWS(bdy)),
    mth, CHOOSECOLS(bdy, 4),
    dts, TOROW(dates, 1),
    tst, (CHOOSECOLS(bdy, 1) <> "") * ISERROR(XMATCH(mth, dts)) + (mth = dts),
    out, CHOOSEROWS(bdy, TOCOL(IFS(tst, rId), 2, 1)),
    VSTACK(hdr, HSTACK(TAKE(out,, 3), TOCOL(IFS(tst, dts), 2, 1), DROP(out,, 4)))
)

        Cheers! 🙂

Resources