Forum Discussion

junbmutuc's avatar
junbmutuc
Copper Contributor
Oct 13, 2024

SPILL RECALC

Hi,   I have a large excel file using Byrow with unique, filter & sumifs functions to extract data. Before, I just press F2 and Enter to return the spill array. Recently, It does now show the fir...
excel
Formulas and Functions
office 365
Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 14, 2024
What does your formula look like?
  • junbmutuc's avatar
    junbmutuc
    Copper Contributor
    Oct 15, 2024

    Patrick2788 

     

    SPILL FORMULA

    =BYROW(SEQUENCE(ROWS(AGENTLIST)),LAMBDA(xROw,
    LET(
    cYEAR,2024,
    cNew,"NEW",
    cSM,INDEX(AGENTLIST,xROw,2),
    cSS,INDEX(AGENTLIST,xROw,3),
    cPOS,INDEX(AGENTLIST,xROw,4),
    cCustomer,UNIQUE(FILTER(SALES_TABLE[CUSTOMER],1
    *(IF(cSM=" ",1,SALES_TABLE[SM]=cSM))
    *(IF(cSS=" ",1,SALES_TABLE[SS]=cSS))
    *(SALES_TABLE[CATEGORY]<>"RAWMAT")
    *(SALES_TABLE[CATEGORY]<>"MATERIAL")
    *(SALES_TABLE[INVYEAR]=cYEAR)
    *(IF(cNew="ALL",1,SALES_TABLE[NEW]=cNew)),"EMPTY")),
    IF(INDEX(cCustomer,1,1)="EMPTY",0,ROWS(cCustomer)))))

     

    The AGENTLIST is a spill array of agent names, team and manager.
    The SALES_TABLE is Excel Table of Raw Sales Data (150,000+ rows)

     

    I hope this helps...

Resources