excel-formula

How do I connect 2 data sets in excel using dynamic functions?

I am building a report for sales people and need to connect two data sets with dynamic functions. Data set 1 is targets, but each individual could have multiple records (e.g. if they have a different leader throughout year. e.g. person a has had 3 leaders. I also need ability to include or exclude records based on selection in the report:

enter image description here

I then need to connect this data with Data set 2, which shows the volumes by account for the accounts signed along with who signed it. I would need the ability to also include or exclude certain records, and some accounts could be split between 2 individuals (account y)

enter image description here

The end result would need to be target and sales volume per leader based on when that leader had volume rolling up to them along the lines of:

enter image description here

as an example, I have the following that pivots the data for targets:

=LET( names, UNIQUE(B2:B5), namesWithYes, FILTER(B2:B5, P2:P5="yes"), TargetWithYes, BYROW(FILTER(C2:O5,P2:P5="yes"),LAMBDA(α,SUM(CHOOSECOLS(DROP(α,,1),--TEXTSPLIT(INDEX(α,1),","))))), Targets, MMULT(--(names=TRANSPOSE(namesWithYes) ), TargetWithYes), IF( {1,0}, names, Targets ) )

Solution

  • If understood correctly, you want to achieve the volumes, if so then could use the following formula:

    enter image description here

    =LET(
     names, UNIQUE(B2:B5),
     namesWithYes, FILTER(B2:B5, P2:P5="yes"),
     TargetWithYes, BYROW(FILTER(C2:O5,P2:P5="yes"),LAMBDA(α,SUM(INDEX(α,--TEXTSPLIT(@α,",")+1)))),
     Targets, MMULT(--(names=TOROW(namesWithYes) ), TargetWithYes),
     Volumes, MAP(names, LAMBDA(α, LET(δ, FILTER(HSTACK(A2:A5,D2:O5),α=B2:B5,""),
              SUM(BYROW(δ, LAMBDA(ε, SUM(IF((INDEX(ε,1)=A8:A11)*(DROP(ε,,1)>0)*(O8:O11="Yes"),C8:N11,0)))))))),
     HSTACK(names, Targets,Volumes))