Forum Discussion
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. ...
=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.
Speaking perhaps just for myself (1) it looks as if you already have what you want to have, so (2) what am I missing. (3) your description of the process doesn't fully make sense. (4) is it possible, since you apparently already have a spreadsheet (in order to make those images), for you to post that actual spreadsheet, with whatever formulas currently exist, posting it on OneDrive or GoogleDrive with a link pasted here that grants access.
But please, in addition to posting the spreadsheet you already have (so that we can help without recreating it from scratch), see if you can describe a bit more precisely such things as:
- what is the user going to be seeing when first seeing the sheet needing input? (what columns are empty, which are full, etc?
- What will that user be entering?
- What will the process be (in English, let us take care of translating to Excel) that fills in other columns (E only? others?) based on that input?
To take one example of something that didn't make sense to me, why does the second row, in what I take to be your "output" sheet, the row with CB-1-2 in column a, WHY does it have the number 15 under configuration type? How did it go from the 30 in that second table to 15? What logic? What rule?