Home > Net >  How do I merge multiple sequences in excel?
How do I merge multiple sequences in excel?

Time:04-27

I am trying to create a list of ordered date for the 3rd of the month, 16 of the month, and every Friday.

It looks like this

    B2 - 2002/1/3
    B3 - 2002/1/16
    B4 - 2020/9/11
    =TRANSPOSE(EDATE(B2,SEQUENCE(36,1,0)))
    =TRANSPOSE(EDATE(B3,SEQUENCE(36,1,0,1)))
    =TRANSPOSE((SEQUENCE(144,1,B4,7)))

I would like it all to be in on row with unique values only. I tried to use LET but I can't figure our how to have them merged into one list.

Thanks!

CodePudding user response:

Using LET and CHOOSE:

=LET(a,EDATE(B2,SEQUENCE(1,36,0)),
b,EDATE(B3,SEQUENCE(1,72,-36,1)),
c,SEQUENCE(1,144 72,B4-(72*7),7),    
d,SEQUENCE(1,144 36 36),
SORT(CHOOSE(IF(d<37,1,IF(d<73,2,3)),a,b,c),1,1,TRUE))

enter image description here

There is a new function currently on available to Office 365 insiders beta channel HSTACK that would simplify this:

=LET(a,EDATE(B2,SEQUENCE(1,36,0)),
    b,EDATE(B3,SEQUENCE(1,36,0,1)),
    c,SEQUENCE(1,144,B4,7),
    SORT(HSTACK(a,b,c),1,1,TRUE))

  • Related