Forum Discussion

LousQuinn's avatar
LousQuinn
Copper Contributor
Dec 09, 2022

Group tranposed data

Hello,   I'm having difficulty 'grouping' the transposed data - so as attached, if the account in colum A matches/is the same, rather than creating separate transposed colums, all amounts will appe...
excel
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 09, 2022

Hi LousQuinn 

 

A 365 alternative assuming you have functions HSTACK & VSTACK:

 

 

In I3:

=LET(
  Header,   TRANSPOSE(UNIQUE(TBL_Source[Account])),
  StackAmt, REDUCE(0,Header,
    LAMBDA(seed,acct, HSTACK(seed, FILTER(TBL_Source[Amount],TBL_Source[Account]=acct)))
  ),
  Clean,    IFNA(DROP(StackAmt,,1),""),
  VSTACK(Header, Clean)
)
LousQuinn's avatar
LousQuinn
Copper Contributor
Dec 12, 2022
Thanks for both you answers
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 12, 2022
    You're welcome. If any of them does what you expect please mark it as solution to help those who search this site - Thanks & nice day...
    • LousQuinn's avatar
      LousQuinn
      Copper Contributor
      Dec 12, 2022
      Ok will test both solutions asap. thanks again
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 27, 2022

        LousQuinn 

        Another 365 option

        in I9:

        =LET(
  JoinAmts, LAMBDA(Amounts,Accounts,account,
      TEXTJOIN(";",,FILTER(Amounts,Accounts=account))
  ),
  Accts, TRANSPOSE(UNIQUE(TBL_Source[Account])),
  StackedValues, REDUCE("",Accts,
    LAMBDA(seed,acct, VSTACK(seed,JoinAmts(TBL_Source[Amount],TBL_Source[Account],acct)))
  ),
  VSTACK(
    Accts,
    TRANSPOSE(IFNA(NUMBERVALUE(TEXTSPLIT(TEXTJOIN("|",TRUE,StackedValues),";","|")),""))
  )
)