Forum Discussion
Solved
How to extract a column value based on value in the range
I have a team ROTA in excel sheet. The first column is the Name and first row is the date. The intervening cells are loaded with values. Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15...
See the attached workbook.
See the attached workbook.
How did you get the Spill getting filled automatically ? Because when I copy person 1 to 5 in from A10-A14 I'm not getting the value as you got.
Not looking same as what you have got ?
How to achieve this ?
=IFNA(REDUCE(HSTACK("","S","P","L","M","N"),A3:A7,LAMBDA(a,b,VSTACK(a,REDUCE(b,HSTACK("S","P","L","M","N"),LAMBDA(u,v,HSTACK(u,IFERROR(TOCOL(FILTER(B1:AE1,FILTER(B3:AE7,A3:A7=b)=v)),""))))))),"")
With Office 365 or Excel for the web this formula could be an alternative.
In entered the formula in B10, then filled it to the right to F10.
I copied B10:F10, then pasted to B12. B14, B16 and B18.
