Here is an example of the data and what I'm trying to do.
No sure how to approach this issue. I've been able to do it before, but I had to create extra sheets with data and do a bunch of VLOOKUPs. Messy and time consuming.
Thought there must be a better way.
I attached a spreadsheet
One way, but you can't put these formulae into a Structured Table. You could, however, restructure your data and then Copy and Paste Special | Values and THEN insert a Structured Table.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
One way:
=LET(D,B5:D10,A,UNIQUE(INDEX(D,,1)),B,TRANSPOSE(UNIQUE(INDEX(D,,2))),VSTACK(HSTACK("Name",B),REDUCE(A,B,LAMBDA(y,z,HSTACK(y,MAP(A,LAMBDA(x,SUMIFS(INDEX(D,,3),INDEX(D,,1),x,INDEX(D,,2),z))))))))
change the bit in RED to suit.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Wow. Thanks for the options. I'm really going to have to walk through these formulas to see what it's actually doing!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks