Forum Discussion
Solved
Concatenate or vlookup or something else?
Hello! My first post and I'm not even really sure what formula I am looking for, but I'll try to explain what I need... Along the top of my spreadsheet are the dates of the month. The first column...
=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,
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