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
I see you're using 365 so this may do what you need:
'Transactions dynamic range
=LET(rng, Sheet1!$A$8:$G$10000, nonblank, COUNTA(Sheet1!$C$8:$C$10000), TAKE(rng, nonblank))
'EGPRef (column b)
=TAKE(DROP(Transactions, , 1), , 1)
=LET(
FilledEGP, SCAN("", EGPRef, LAMBDA(a, v, IF(v = "", a, v))),
uEGP, TOCOL(EGPRef, 1),
DROP(
REDUCE(
"",
uEGP,
LAMBDA(a, v,
LET(
filtered, FILTER(Transactions, FilledEGP = v),
IFERROR(VSTACK(a, TOROW(filtered, 1)), "")
)
)
),
1
)
)i am trying to use the formula yove been provided but it wasnt work as per the attached,
your support is highly appreciated
The formula looks good. That workbook is missing the "EGP Ref" the previous copy had:
- It takes long time for calculation and make the sheet not responding.
N.B: the sheet including large row data
- 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 sheetIt 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.
