Dec 09 2022 02:00 AM
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 appear under the matching account. thanks
Dec 09 2022 04:23 AM - edited Dec 09 2022 06:40 AM
Dec 09 2022 11:19 PM
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)
)
Dec 12 2022 01:26 AM
Dec 27 2022 04:24 AM
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),";","|")),""))
)
)