Forum Discussion
Solved
help with a formula please
Hi, Im hoping you could please help me with this formula as I cant do it after lots of tries! I have 2 sheets. One is a front page and one is a data page. The front page contains a unique numbe...
If you are on Excel 365 it could be
=LET( number, data!$A$2:$A$29, instruction, data!$B$2:$E$29, uniqueNumber, $A$2:$A$22, getInstruction, LAMBDA(num, LET( f, FILTER(instruction, number=num), n, BYROW(f, LAMBDA(v, SUM(--(v<>"") ) ) ), IFERROR(TEXTJOIN(" ",, TAKE( FILTER(f, n=MAX(n) ), 1 ) ), "" ) ) ), MAP( uniqueNumber, getInstruction ) )
If you are on Excel 365 it could be
=LET(
number, data!$A$2:$A$29,
instruction, data!$B$2:$E$29,
uniqueNumber, $A$2:$A$22,
getInstruction, LAMBDA(num,
LET(
f, FILTER(instruction, number=num),
n, BYROW(f, LAMBDA(v, SUM(--(v<>"") ) ) ),
IFERROR(TEXTJOIN(" ",, TAKE( FILTER(f, n=MAX(n) ), 1 ) ), "" )
)
),
MAP( uniqueNumber, getInstruction )
)
Thank you so much really appreciate your help. This is a very complex formula that I would never have been able to do.
Rachel1950 , you are welcome