admin管理员组

文章数量:1122832

I am attempting to create a formula, that will create a string of consolidated dates by the minimum and maximum, separated by cells containing single digits.

The formula I've been attempting to manipulate is listed below:

=UNIQUE(FILTER(MIN(B1:P1),LEN(B1:P1)>1,""))

I am attempting to create a formula, that will create a string of consolidated dates by the minimum and maximum, separated by cells containing single digits.

The formula I've been attempting to manipulate is listed below:

=UNIQUE(FILTER(MIN(B1:P1),LEN(B1:P1)>1,""))
  • excel
  • excel-formula
  • lag
  • timeline
Share Improve this question edited Nov 21, 2024 at 15:43 Mayukh Bhattacharya 27k8 gold badges29 silver badges42 bronze badges asked Nov 21, 2024 at 14:28 brandon campbellbrandon campbell 454 bronze badges 1
  • Correct, Row 3 is the desired output. – brandon campbell Commented Nov 21, 2024 at 14:58
Add a comment  | 

1 Answer 1

Reset to default 1

You could try using anyone of the following solutions:

• Formula used in cell A3

=LET(
     a, B1:P1, 
     b, IF(a>400,a,"-"), 
     c, TEXTSPLIT(ARRAYTOTEXT(b),,"-"), 
     REDUCE("End Result",c,LAMBDA(x,y,HSTACK(x,
     AGGREGATE({15,14},6,--TEXTSPLIT(y,,", ",1),1)))))

• Or, Using GROUPBY():

=LET(
     a, TOCOL(B1:P1),
     b, SCAN(1,--(a<365),SUM)/(a>365),
     HSTACK("End Result",TOROW(DROP(GROUPBY(b, a, 
     HSTACK(MIN,MAX),,0,,ISNUMBER(b)),1,1))))

• Or:

=LET(
     a, TOCOL(B1:P1),
     b, SCAN(1,--(a<365),SUM)/(a>365),
     REDUCE("End Result",UNIQUE(TOCOL(b,3)),LAMBDA(x,y,
     HSTACK(x,AGGREGATE({15,14},6,a/(b=y),1)))))

本文标签： excelConsolidated Date windowStack Overflow

版权声明：本文标题：excel - Consolidated Date window - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736309981a1934215.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。