CODEWITHSUNDEEP
excelexcel-formula
jrjrjr12345
jrjrjr12345

Reputation: 1

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

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

Upvotes: 0

Views: 99

Answers (2)

P.b
P.b

Reputation: 11483

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.

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36880

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 FILTERXM() by JvdV.

enter image description here

Upvotes: 1

Related Questions