Forum Discussion
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 number with a special instruction. The information is from the data tab. This unique number is multiple times on the data sheet with special instructions and there are four different columns with the special instructions (unable to combine in 1 column as all looking for different things from different columns). I have concatenate the line to bring the special instruction which it does. On the front page I would like it to show the line with the maximum instructions but it just pulls through the special instruction from the first line with the unique numbers. Is there a way to do this or is it just not possible?
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 ) )
5 Replies
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
=IFNA(INDEX($L$2:$L$30,MATCH(1,($G$2:$G$29=A2)*(LEN($L$2:$L$29)=MAX(LEN(IF(A2=$G$2:$G$29,$L$2:$L$29,0)))),0)),"")
Does this return the intended result? The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
- thank you for your help and it would produce the required information that is needed if I combined all the data in one sheet.
