Forum Discussion

LousQuinn's avatar
LousQuinn
Tin 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
LousQuinn's avatar
LousQuinn
Tin 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
    Tin 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),";","|")),""))
  )
)