Forum Discussion
Solved
Formula assist in office 365
Dears, Could you please advice, the attached sheet including data exported from bank and i need to make the discrptions in culumn #C appear in one line not rows as the example in culmn #J Thanks...
Perhaps with Power Query if you consider such option
the formula shown as the below
=LET(FilledEGP,_xlfn.SCAN("",EGPRef,_xlfn.LAMBDA(_xlpm.a,_xlpm.v,IF(_xlpm.v="",_xlpm.a,_xlpm.v))),uEGP,_xlfn.TOCOL(EGPRef,1),_xlfn.DROP(_xlfn.REDUCE("",uEGP,_xlfn.LAMBDA(_xlpm.a,_xlpm.v,LET(filtered,FILTER(Transactions,FilledEGP=_xlpm.v),IFERROR(_xlfn.VSTACK(_xlpm.a,_xlfn.TOROW(filtered,1)),"")))),1))
when i copied it to my sheet
=LET(FilledEGP,_xlfn.SCAN("",EGPRef,_xlfn.LAMBDA(_xlpm.a,_xlpm.v,IF(_xlpm.v="",_xlpm.a,_xlpm.v))),uEGP,_xlfn.TOCOL(EGPRef,1),_xlfn.DROP(_xlfn.REDUCE("",uEGP,_xlfn.LAMBDA(_xlpm.a,_xlpm.v,LET(filtered,FILTER(Transactions,FilledEGP=_xlpm.v),IFERROR(_xlfn.VSTACK(_xlpm.a,_xlfn.TOROW(filtered,1)),"")))),1))
when i copied it to my sheet
It looks like you're using an older version of Excel that does not support SCAN, DROP, TOCOL, REDUCE, etc.
You do seem to have access to LET which tells me the version might be Excel 365 but it's not fully updated.
- is there any way to replace it with a sample formula to do that