Forum Discussion
Excel form al for creating number list (1, 2, 3, 4) based on value in previous cell
- Apr 12, 2023
Thanks. This will work as far as 'XFD' - let me know if you'll need to go beyond that.
=LET( ζ,A2:INDEX(A:A,MATCH(7^89,A:A)), BYROW(ζ,LAMBDA(ξ, TEXTJOIN(",",, SUBSTITUTE( ADDRESS(1,SEQUENCE(ξ,,SUM(A2:ξ)-ξ+1),4),1,"" ) ) ) ) )
Regards
Amazing that worked!!!
Now if you're really smart, for our employees they have to be assigned letters A, B, C , . . .AA, BB, CC, . . .AAA, BBB, CCC
Will it work for letters like that?
Don't have as many employee sales so it might not be worth the trouble.
I don't understand how this fits in. Is this instead of the above counting or for certain conditions or something else? As for your counting let me note that counting using letters only SHOULD go: A, B, C, ... , Y, Z, AA, AB, AC, ... and NOT AA, BB, ...
IF that is OK then there is a simple trick in excel to use the ADDRESS function to have Excel convert for you as long as a max of XFD (i.e. 16384) is high enough. The trick is:
=SUBSTITUTE(ADDRESS(1,NNN,4),"1","")
Put any number (or reference to a number) in NNN and it will output the corresponding letters.
So w/r to the above equation here is it modified for letters:
=LET(
ζ,A2:INDEX(A:A,MATCH(7^89,A:A)),
BYROW(ζ,LAMBDA(ξ,
TEXTJOIN(",",,SUBSTITUTE(ADDRESS(1,SEQUENCE(ξ,,SUM(A2:ξ)-ξ+1),4),"1","")))
)
)
- Lauren_Hofstetter21Apr 13, 2023Copper ContributorYou amaze me, I'm only self taught in Excel, and have learned a lot but this is much appreciated!
- mtarlerApr 13, 2023Silver ContributorLauren you are welcome but it looks like JosWoolley had you covered.
JosWoolley , I didn't mean to essentially repeat your same answer but I swear when I posted my reply your reply wasn't there. Sometimes this board (or maybe my computer) does funny things- JosWoolleyApr 13, 2023Iron ContributorNot at all, your input is most appreciated! I've been working on forums long enough that I've seen so many examples of 'post crossover' that it's really not a surprise, nor an issue.
Cheers