Forum Discussion

mb454316's avatar
mb454316
Copper Contributor
Apr 03, 2024
Solved

Need help building a numbering formula

Okay so I have these two tables. In table 1 I would like the “INPUT” column (column D) to populate the Input numbers differently but I can’t figure out a formula to do what I am intending.   ...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 03, 2024

    mb454316 

    =LET(rng,H3:K23,

    numbering,DROP(

    REDUCE("",SEQUENCE(,COLUMNS(rng)),LAMBDA(a,b,

    LET(

    z,IF(b=1,0,COUNT(CHOOSECOLS(rng,SEQUENCE(,b-1,1,1)))),

    HSTACK(

    a,

    REDUCE("",SEQUENCE(ROWS(rng)),LAMBDA(u,v,

    VSTACK(u,IF(INDEX(rng,v,b)="","",z+COUNT(INDEX(rng,1,b):INDEX(rng,v,b))))))))))

    ,1,1),

    "INPUT "&BYROW(numbering,LAMBDA(r,FILTER(r,r<>""))))

     

    This formula returns the intended result in my sheet. However it only works in Office 365 and Excel for the web.

     

     

    For verification i've applied the formula for range M3:P23 and it returns the expected result.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 03, 2024

mb454316 

The steps in this 365 calculation

= LET(
    n,        ROWS(data),
    MPPT,     BYROW(data, LAMBDA(d,
                  XLOOKUP(1, d, MPPT_Hdr, , 1)
              )),
    input,    "INPUT " & TEXT(SEQUENCE(n),"00"),
    labelled, HSTACK(SORTBY(CB, MPPT), input),
    HSTACK(SORT(labelled), MPPT)
  )

are to use a horizontal XLOOKUP to return the relevant MPPT from the header.  The CB column is sorted by MPPT and an input sequence attached to each row.  Re-sorting by CB and reattaching the new MPPT column returns the output table.

Resources