Love the use of DAs!🚀 I think the issue at 6:16 is that you are comparing two differently sized arrays: B13:B1058 and Z13# (which excludes duplicates). This is done pairwise and #N/A's are returned for the missing pairs. When you instead do just Z13, we compare each cell in B13:B1058 against the scalar value in Z13 and that correctly returns an array of booleans.
Yes, I realized that in my head I was trying to make it do a 3D array collapsing to 2D, but that’s not how it works! I did an array version of that on an earlier attempt, but for that one I tagged the lap number in the original data first, and then I was able to do a SUMIFS on both fields. Maybe you can put a 3D version of TRANSPOSE in the next release? ; )
The 2D from 3D array is require MS insider Lambda function =LET(r,B13:B1058,t,C13:C1058,s,UNIQUE(r),MAP(IF(AB12#,s),IF(s,AB12#),LAMBDA(rr,n,LET(l,FILTER(t,r=rr),IFERROR(SMALL(l,n+1)-SMALL(l,n),""))))) Without Lambda function would be very complicated =LET(r,B13:B1058,t,C13:C1058,s,UNIQUE(r),sr,SORTBY(r,MATCH(r,s,)),st,SORTBY(t,MATCH(r,s,)),q,SEQUENCE(ROWS(r)),f,FILTERXML(CONCAT("",IFERROR(IF(INDEX(sr,q+1)=sr,TEXT(INDEX(st,q+1)-st,"hh:mm:ss-"),""),"")),"//m"),IFERROR(--MID(f,SEQUENCE(,10,,9),8),""))
Love the use of DAs!🚀 I think the issue at 6:16 is that you are comparing two differently sized arrays: B13:B1058 and Z13# (which excludes duplicates). This is done pairwise and #N/A's are returned for the missing pairs. When you instead do just Z13, we compare each cell in B13:B1058 against the scalar value in Z13 and that correctly returns an array of booleans.
Yes, I realized that in my head I was trying to make it do a 3D array collapsing to 2D, but that’s not how it works! I did an array version of that on an earlier attempt, but for that one I tagged the lap number in the original data first, and then I was able to do a SUMIFS on both fields.
Maybe you can put a 3D version of TRANSPOSE in the next release? ; )
The 2D from 3D array is require MS insider Lambda function
=LET(r,B13:B1058,t,C13:C1058,s,UNIQUE(r),MAP(IF(AB12#,s),IF(s,AB12#),LAMBDA(rr,n,LET(l,FILTER(t,r=rr),IFERROR(SMALL(l,n+1)-SMALL(l,n),"")))))
Without Lambda function would be very complicated
=LET(r,B13:B1058,t,C13:C1058,s,UNIQUE(r),sr,SORTBY(r,MATCH(r,s,)),st,SORTBY(t,MATCH(r,s,)),q,SEQUENCE(ROWS(r)),f,FILTERXML(CONCAT("",IFERROR(IF(INDEX(sr,q+1)=sr,TEXT(INDEX(st,q+1)-st,"hh:mm:ss-"),""),"")),"//m"),IFERROR(--MID(f,SEQUENCE(,10,,9),8),""))
Thank you for this one too
🙌 beautiful work