Create Array of Letters and Numbers

I have looked at your sample spreadsheet and have a few questions.

Is there a maximum size (rows and columns) to your framework?
Are the order and location of the identities within the framework critical (I note there are gaps in the 2nd row of your sample.)?
How are you going to use this data, once collected? There are no formulae referring to these cells.


Stephen

Explain HOW your expected answer should be derived.

One of the major issues I have with sample files is the inclusion of too much irrelevant stuff in a sheet. It leaves no room for experimentation, and totally obsures the message.

I have cut your sheet back to the bare bones and I think I follow it. I'm not saying I can help... but I do need some clarification.



1. Check the file. Answer ALL of the questions I have asked. Correct me where I'm wrong.

2. Include 2 further examples, using the model I have used, of input and desired result.

Then we'll see...

I've played with this for just over an hour... painful! IF my understanding is correct, try this.

Helper column (to generate sprinklers per row):

=IF(ROWS(U$3:U3)<=ROUNDUP($B$5/$B$4,0),MIN($B$4,$B$5-SUM(U$2:U2)),"")


The fixed point, around which the table is generated is the M number, copied down:

=IF(ROWS(L$3:L3)>ROUNDUP($B$5/$B$4,0),"","M"&ROWS(L$3:L3))


The S numbers to the LEFT of M are generated using this, copied to the LEFT and down:

=LET(eR,$B$4*(ROWS(K$3:K3)-1),lM,IF($U3="","",IF($U3<$B$4,ROUNDUP($U3/2,0),$B$3)),c,COLUMNS(K3:$K3),IFERROR(IF(lM-(c-1)<1,"",IF(c<=lM,"S"&eR+lM-(c-1))),""))


The S numbers to theRIGHT of M are generated using this, copied to the RIGHT and down:

=LET(c,COLUMNS($M3:M3),lP,MID($K3,2,255),er,$B$4*(ROWS(M$3:M3)-1),IFERROR(IF(lP-er+c>$U3,"","S"&lP+c),""))

Does it work?

V2... without the helper.

Here is a much LESS sophisticated approach to the same problem.

Perhaps its most obvious disadvantage is that it requires you to find space on your worksheet for 3 extra tables. However, I like this mode of approach because the logic appears clearer - at least to me.

Originally Posted by ScoRat

Do you know if there's an option to substitute the let function to make it usable with Excel 2016?

My solution at #8 was built in EXCEL 2013 and so would be useable in your excel version.

Oh dear, sorry about that. I was certain you were using O365.

I hope you were able to test it on an O365 system beofre saying it was OK...

I set up a series of Named Ranges to do the intermediate calculations and (I think) I have reproduced it all on your original sheet.

Test & report back....

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.