Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Copper Contributor
May 03, 2023
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 in advance

excel

17 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 03, 2023

    Hussein_Mohamed 

    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
    )
)
    • Hussein_Mohamed's avatar
      Hussein_Mohamed
      Copper Contributor
      May 03, 2023
      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
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        May 03, 2023

        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.

Resources