SOLVED … 🥳! Thanks to all of you!! Hi everybody, Im looking for someone that could help me to find out the correct formulas in the left table.
SOLVED … 🥳! Thanks to all of you!!
Hi everybody,
Im looking for someone that could help me to find out the correct formulas in the left table. So taking reference in the right table & just by dialing the workers ID in the 1st column (left table) the working timetable should appear for the above mentioned date.
Many thanks for you help!
Replies
Gaetano Sportelli refer image below for a solution
• Formula used in cell C3
=FILTER($K$3:$K$26,($A3=$I$3:$I$26)($B3=$J$3:$J$26)(C$2=$L$3:$L$26))
Gaetano Sportelli not at all a proper screenshot. I cant see anything this is so small ?
Gaetano Sportelli do you need a video explanation ?
Mayukh Bhattacharya. Here’s a better screenshot
Gaetano Sportelli ok, no issues let me create a video as well, to show its working for me as per the data you have provided
Gaetano Sportelli please check the last comment in the thread posted a video
Gaetano Sportelli if you are in MS365 and in current channel then you can use one formula to transform the data like as below
• Formula used in cell A2
=LET(X,I3:I26,VSTACK(HSTACK({"Worker-ID","Name"},TRANSPOSE(UNIQUE(L3:L26))),IFERROR(UNIQUE(HSTACK(I3:I26,J3:J26,MAKEARRAY(ROWS(X),MAX(COUNTIF(I3:I26,I3:I26)),LAMBDA(r,c,INDEX(FILTER(K3:K26,X=INDEX(X,r)),c))))),"")))
Just see my this video and you will get the answer
Data Analysis Guru based on how the data is set up, a simple vlookup is not going to retrieve all the data that he is looking for
Data Analysis Guru Thanks for feedback by no link to your video joined!
Gaetano Sportelli can you please refer the solutions I have posted. And let me know whether it is working for you. I have posted in the group where post was approved earlier.
Gaetano Sportelli here is a video using INDEX() & MATCH() Function
Gaetano Sportelli here is the video for you using FILTER() Function
Gaetano Sportelli and here is the video using the LAMBDA() function, hope it helps. One spill array formula which spills. So you don't have to drag or do nothing, just enter the formula in cell A2
Gaetano Sportelli if you are not using MS365 then use INDEX() & MATCH() Function
• Formula used in cell C3
=IFERROR(INDEX($K$3:$K$26,MATCH(1,($A3=$I$3:$I$26)($B3=$J$3:$J$26)(C$2=$L$3:$L$26),0)),"")
And Fill Down & Right for the cells accordingly !!!
You really should show some expected results here to know what / how you want the table filled. what do you want for the dates, the date or a check mark or whtat, what happens if they don't work that day? everything is exactly the same for each person, just that if its not a true representation of the real data, then you may not get the formulas you are looking for. Also what happens on the 5th, 6th..... And so on for the month?
Erik Guzik Thanks for feedback! My Pics shows just a sample of the full file which takes in consideration a certain period for several workers. The requested result should be:
Gaetano Sportelli ok. Looks like you have a formula. Always helpful to state the version of Excel you have. These function make it easier to do this
Erik Guzik . I sent this to him, looks like he say it is working...
