Filter google spreadsheet if the values of people with the same identity coincide formula
The image in the Google spreadsheet contains the information of the people; If a person works in more than one store, it shows the working days one below the other, and writes "X" or "H" in the place of the day he worked in each store. If there are more than one identical id numbers in column B and the people's shifts overlap, that is, if the "X" s overlap (or "H", it doesn't matter what it says), the filtering formula
The formula below brings the combination but also the incompatible ones and gives the wrong result.
=FILTER(TEST1!B3:AH, COUNTIF(TEST1!B3:B, TEST1!B3:B) > 1, BYROW(TEST1!D3:AH, LAMBDA(X, SUM(LEN(X) <> "") > 1)))
My expectation is this; when an employee works in more than one store, I mark the days he works with an X (or text), and if I accidentally marked an X in both stores on the same day, I want to filter them and then manually correct them. For example, there should not be an X in both stores on the 12th of the month because both stores cannot work at the same time, it should be in one. If any of them overlap as a result of the filtering, the entire list belonging to that person should be visible. As a result, the list in the photo below should appear;
AND THE LAST RESULT ;
I WANT TO SEE THE INTERSECTING DAYS
You may try:
=let(Σ,tocol(,1),reduce(Σ,unique(tocol(TEST1!B3:B,1)),lambda(a,c,vstack(if(iserr(+a),Σ,a),let(Λ,bycol(filter(TEST1!D:AH,TEST1!B:B=c),lambda(y,if(counta(y)>1,"X",))),
if(counta(Λ)=0,Σ,hstack(chooserows(filter(TEST1!A:C,TEST1!B:B=c),1),Λ)))))))
