How to create a list of dates with the exact number of duplicates in excel?-CodePudding

I have a column that has dates and right next to it, I have another column that has a number. What I want to do is write formula in excel that will create a new list that has each date repeated the exact number of times. See below for example:

Column A Nov-22 Dec-22 Jan-23 Column B 2 2 1

New Column:

Nov-22 Nov-22 Dec-22 Dec-22 Jan-23

CodePudding user response：

FILTERXML() with REPT() function may give you desired result.

=TEXT(FILTERXML("<t><s>"&TEXTJOIN("",TRUE,REPT(A1:A3&"</s><s>",B1:B3))&"</s></t>","//s[node()]"),"mmm-yy")

Read this to know more about

CodePudding user response：

Another approach requiring Office 365, but not limited to Windows environment (like FILTERXML):

=LET(data,A1:B3,
          month,INDEX(data,,1),
          freq,INDEX(data,,2),
          seq,SEQUENCE(SUM(freq)),
          cum,SCAN(0,freq,LAMBDA(a,b,a b)),
INDEX(month,XMATCH(seq,cum,1)))

It indexes the data to month and frequency, then it indexes the month to the closest (exact or next larger) match of the sequence of the sum of the frequency to the cumulative sum of the frequency.