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 is a list of driver violations.
I'm using TRANSPOSE and XLOOKUP to grab the dates of each violation, however, some violations happen on several consecutive days. Instead of lising several consecutive dates in a column, is there a way to return the date of the first violation in a column and the last consecutive date in another column, or return for example October 1 - October 5 in one column?
Thank you for any help you can offer!
Dawn-Marie
=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.
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
Could you upload a sample workbook without sensitive data to Onedrive, Google Drive, Dropbox or similar, obtain a link that lets use view/download the workbook, and past that link into a reply?
Hi HansVogelaar,
Thank you for the reply!
Not sure if this will work, but here is the link:
https://onedrive.live.com/edit.aspx?resid=94b6220b142f4524!79714&cid=94b6220b142f4524&CT=1740101068578&OR=ItemsView
I'm almost where I need to be, but not quite. Hoping to get a solution!
Thank you!
The link doesn't work for me.
This is highly possible. Is it also possible for you to send a screenshot of what you mean or a worksheet?
Hi NnyiimockBitanyanmi,
Thank you for the reply!
The first screenshot is my data entry sheet where I enter which violations happened on which day.
The second screenshot is the report I supply to my client.
I almost have a solution, except that sometimes a driver doesn't have a specific violation, or he only has one, so there isn't a range for the dates, just one single date.
If possible, it would be nice to have a - in front of the second date so it says 11/02/2024 - 11/03/2024, but I'm not sure if that is possible!!
I appreciate any help, thank you!
