Forum Discussion
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 me give you some background so you understand what is going on here and it will be easier to ask and answer my question. Column E values are populated based on the information in the second table. For example, if CB-1-1 has a value in the MPPT 1 column in the second table, column E will return “MPPT 1” for CB-1-1 in the first table. The CB count at the bottom of the second table tells you how many CBs there are on each MPPT, which tells you how many inputs you need per MPPT group.
I want there to be 21 total inputs but I want the first inputs to be all of the MPPT 1s followed by the MPPT 2s, followed by the MPPT 3s, and then MPPT 4s. Here is a visual for what I want it to look like:
Is there a formula that can do this? I have been working on it and I have a few pieces of the formula that I think I need but I can’t figure out how to get it to work together and am having a hard time determining how to get it to continue the numbering as it switches to the next MPPT group. I also need this to work when the second table is different. For example, if CB-1-1 has value in the MPPT 2 column instead of the MPPT 1 column. Your formula can just populate the numbers without "INPUT" in front of it if you want. I can add the "INPUT" myself afterwards with the CONCATENATE function.
=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.
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.
=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.
- Thank you! I did have a formula for the MPPT column, but thanks!
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?
mb454316 In case you don't have Excel 365 you can also do it with old formulas like this:
="INPUT "& COUNT(INDEX($H$3:$K$3,MATCH(E3,$H$2:$K$2,0)):INDEX($H3:$K3,MATCH(E3,$H$2:$K$2,0)))+ SUM($G$24:INDEX($G$24:$J$24,MATCH(E3,$H$2:$K$2)))line 2 will add up all the cells with number from this line up to the top of the column for this MPPT
line 3 will add up the CB Counts for MPPT columns before this MPPT
see attached
BTW I also made a formula for the MPPT column in case you were manually entering that before
