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

Question

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

score 1 · Answer 1 · answered Aug 19 '22 at 02:37

1

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.

answered Aug 19 '22 at 02:37

Harun24hr

30,391
4
21
36

this is keeping them as text, is there anyway to keep it as a date – jrjrjr12345 Aug 19 '22 at 05:24
@jrjrjr12345 Then do not use `TEXT()` function. Only use `FILTERXML()`. Try- `FILTERXML("~~"&TEXTJOIN("",TRUE,REPT(A1:A3&"",B1:B3))&"~~","//s[node()]")`. – Harun24hr Aug 19 '22 at 05:29
The format your resulting cells to desired date format. – Harun24hr Aug 19 '22 at 05:29

score 0 · Answer 2 · answered Aug 19 '22 at 10:51

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.

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

2 Answers2