Forum Discussion

djs72's avatar
djs72
Copper Contributor
May 09, 2023

Script to count and total varying window sizes

Hi.

 

I'm working on a spreadsheet and I want to create a script where it will look at the plan name (Column A), then take the window sizes from Columns B and C and the Optional "z" and total them in Cloumn D.

 

This is how it looks currently:

 

And here's how I want it to look:

And then all subsequent plans after Alpina would be the same.

 

I have roughly 8000 lines in total so any automation would be most appreciated!

 

Thanks!

 

Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    May 10, 2023

    djs72 

    Another 365 option

     

     

    EDITED (shortened):

    =LET(
  uniq,  UNIQUE(IF(TableInput="", "", TableInput)),
  k,     SEQUENCE(4,,,0),
  count, LAMBDA(r, SUM(--(MMULT(--(TableInput=r),k)=4))),
  VSTACK(
    HSTACK(TableInput[#Headers],"TOTAL"),
    HSTACK(uniq, BYROW(uniq, count))
  )
)

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 09, 2023

    djs72 

    An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

    • djs72's avatar
      djs72
      Copper Contributor
      May 09, 2023
      Thanks Quadruple! This looks interesting and I'll give it a shot as well
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 10, 2023

        djs72 

        =LET(range,A2:D17,rangeunique,UNIQUE(IF(range="","",range)),HSTACK(rangeunique,MAP(CHOOSECOLS(rangeunique,1),CHOOSECOLS(rangeunique,2),CHOOSECOLS(rangeunique,3),CHOOSECOLS(rangeunique,4),LAMBDA(colF,colG,colH,colI,SUMPRODUCT((CHOOSECOLS(range,1)=colF)*(CHOOSECOLS(range,2)=colG)*(CHOOSECOLS(range,3)=colH)*(CHOOSECOLS(range,4)=colI))))))

        With Office 365 or Excel for the web you can apply this formula.

         

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 09, 2023

    djs72 

    In E2 (not in column D!):

    =COUNTIFS($A$2:$A$10000, A2, $B$2:$B$10000, B2, $C$2:$C$10000, C2, $D$2:$D$10000, D2)

    Fill down.

    • djs72's avatar
      djs72
      Copper Contributor
      May 09, 2023
      oops! Yes I meant Column E, thanks Hans. I'll give this a shot now

Resources