Forum Discussion

Anu2021's avatar
Anu2021
Copper Contributor
Dec 10, 2025

Need help with excel

  Hi, I need help with below sample data, i have close to 2lakh + records where the data is represented M1,M2,etc basis the start date which will vary for every record   
Formulas and Functions
IlirU's avatar
IlirU
Brass Contributor
Dec 11, 2025

Hi Anu2021,

I am offering you a solution through a formula. This formula works for Excel 365. I have applied this formula in cell A9.

I hope this helps.

 

=LET(
data, A2:N6,
  dr, DROP(data, 1), tk, TAKE(dr,, 2),
  em, EOMONTH(TOCOL(tk), 0), min, MIN(em),
 unq, UNIQUE(EOMONTH(SEQUENCE(, MAX(em) - min + 1, min), 0), TRUE),
      VSTACK(HSTACK(TAKE(TAKE(data, 1),, 2), unq),
      HSTACK(tk, IFERROR(--TEXTSPLIT(TEXTJOIN(";", FALSE,
      BYROW(dr, LAMBDA(a, ARRAYTOTEXT(IFERROR(
      1 / (1 / INDEX(IFERROR(HSTACK(SIGN(SEQUENCE(, XMATCH(TRUE,
      EOMONTH(TAKE(a,, 1), 0) = unq) -1)) - 1,
      DROP(a,, 2)), DROP(a,, 2)),,
      SEQUENCE(, COUNT(unq)))), ""))))), ", ", ";"), "")))
)

 

Resources