Forum Discussion
Concatenate or vlookup or something else?
=LET(range,A1:O9, database,VSTACK(HSTACK("",DROP(TAKE(range,1),,1)),FILTER(DROP(range,1), BYROW(DROP(range,1,1),LAMBDA(x,SUM(N(x="x")))))), IFNA(DROP(REDUCE("",SEQUENCE(ROWS(DROP(database,1))),LAMBDA(u,v, VSTACK(u, LET( rng,TRANSPOSE(VSTACK(TAKE(DROP(database,,1),1),CHOOSEROWS(DROP(DROP(database,,1),1),v))), a,SCAN(0,INDEX(rng,,2),LAMBDA(ini,arr,IF(arr="x",ini+1,0))), b,VSTACK(DROP(a,1),0),c,VSTACK(0,DROP(a,-1)), mapend,MAP(a,b,LAMBDA(aa,bb,IF(AND(aa>1,bb=0),1,0))), mapstart,MAP(a,b,c,LAMBDA(aaa,bbb,ccc,IF(AND(aaa=1,bbb=2,ccc=0),1,0))), start,FILTER(CHOOSECOLS(rng,1),mapstart), end,FILTER(INDEX(rng,,1),mapend), nonconsecutive,FILTER(INDEX(rng,,1),(a=1)*(b=0)), dates,VSTACK(IFERROR(nonconsecutive,""),IFERROR(HSTACK(start,"- "&TEXT(end,"TT.MM.JJJJ")),"")), filtereddates,FILTER(dates,LEN(INDEX(dates,,1))>0), sorteddates,SORTBY(filtereddates,INDEX(filtereddates,,1),1), HSTACK(INDEX(DROP(TAKE(database,,1),1),v),sorteddates))))),1),""))Hello Dawn-Marie,
you are welcome. Unfortunately i didn't understand what you exactly want to do. I was only focussing on consecutive dates which are at least two dates in a row in my understanding. The new formula in addition works if there is only one date like for August or if there isn't any date as for June in my example.
=IFNA(DROP(REDUCE("",SEQUENCE(ROWS(B2:O6)),LAMBDA(u,v,
VSTACK(u,LET(rng,TRANSPOSE(VSTACK(B1:O1,CHOOSEROWS(B2:O6,v))),
a,SCAN(0,INDEX(rng,,2),LAMBDA(ini,arr,IF(arr="x",ini+1,0))),
b,VSTACK(DROP(a,1),0),
c,VSTACK(0,DROP(a,-1)),
mapend,MAP(a,b,LAMBDA(aa,bb,IF(AND(aa>1,bb=0),1,0))),
mapstart,MAP(a,b,c,LAMBDA(aaa,bbb,ccc,IF(AND(aaa=1,bbb=2,ccc=0),1,0))),
start,FILTER(CHOOSECOLS(rng,1),mapstart),
end,FILTER(INDEX(rng,,1),mapend),
HSTACK(INDEX(A2:A6,v),start,end))))),1),"")This formula returns the intended result in Office 365 or Excel for the web in my sample sheet.
Hello OliverScheurich,
I replied to your answer, but it seems to have disappeared. Thank you for the reply!
Your formula worked perfect, except it did not take into account that sometimes there wouldn't be an x beside, in your sample, A. My spreadsheet is driving violations and the drivers don't always have violations. Still a work in progress, but I'm getting there!
=LET(range,A1:O9, database,VSTACK(HSTACK("",DROP(TAKE(range,1),,1)),FILTER(DROP(range,1), BYROW(DROP(range,1,1),LAMBDA(x,SUM(N(x="x")))))), IFNA(DROP(REDUCE("",SEQUENCE(ROWS(DROP(database,1))),LAMBDA(u,v, VSTACK(u, LET( rng,TRANSPOSE(VSTACK(TAKE(DROP(database,,1),1),CHOOSEROWS(DROP(DROP(database,,1),1),v))), a,SCAN(0,INDEX(rng,,2),LAMBDA(ini,arr,IF(arr="x",ini+1,0))), b,VSTACK(DROP(a,1),0),c,VSTACK(0,DROP(a,-1)), mapend,MAP(a,b,LAMBDA(aa,bb,IF(AND(aa>1,bb=0),1,0))), mapstart,MAP(a,b,c,LAMBDA(aaa,bbb,ccc,IF(AND(aaa=1,bbb=2,ccc=0),1,0))), start,FILTER(CHOOSECOLS(rng,1),mapstart), end,FILTER(INDEX(rng,,1),mapend), nonconsecutive,FILTER(INDEX(rng,,1),(a=1)*(b=0)), dates,VSTACK(IFERROR(nonconsecutive,""),IFERROR(HSTACK(start,"- "&TEXT(end,"TT.MM.JJJJ")),"")), filtereddates,FILTER(dates,LEN(INDEX(dates,,1))>0), sorteddates,SORTBY(filtereddates,INDEX(filtereddates,,1),1), HSTACK(INDEX(DROP(TAKE(database,,1),1),v),sorteddates))))),1),""))Hello Dawn-Marie,
you are welcome. Unfortunately i didn't understand what you exactly want to do. I was only focussing on consecutive dates which are at least two dates in a row in my understanding. The new formula in addition works if there is only one date like for August or if there isn't any date as for June in my example.
This worked perfect!
Thank you so much!!
Dawn-Marie
Hello OliverScheurich,
Thank you so much for the reply.
This formula is perfect, except it doesn't take into account that sometimes there are no violations.. in your sample, let's say there's nothing beside C, or there's only one date beside C.
Dawn-Marie
