Forum Discussion

Lauren_Hofstetter21's avatar
Lauren_Hofstetter21
Copper Contributor
Apr 12, 2023

Excel form al for creating number list (1, 2, 3, 4) based on value in previous cell

I'm creating a list for golf balls sold for a golf ball drop. First column will have number of golf balls purchased  Next column will give the numbers of the golf balls.  For example if they purch...
Formulas and Functions
  • JosWoolley's avatar
    JosWoolley
    Apr 12, 2023

    Lauren_Hofstetter21 

     

    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

     

JosWoolley's avatar
JosWoolley
Iron Contributor

Lauren_Hofstetter21 

 

Assuming your first entry is in cell A2, enter this formula in cell B2, which will automatically expand as new entries are made in cells A3, A4, etc.:

=LET(
    ζ,A2:INDEX(A:A,MATCH(7^89,A:A)),
    BYROW(ζ,LAMBDA(ξ,
        TEXTJOIN(",",,SEQUENCE(ξ,,SUM(A2:ξ)-ξ+1)))
    )
)

 

Lauren_Hofstetter21's avatar
Lauren_Hofstetter21
Copper Contributor
Apr 12, 2023

JosWoolley 

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.

  • JosWoolley's avatar
    JosWoolley
    Iron Contributor
    Apr 12, 2023
    Can you just clarify with a new small example dataset with expected results?

    Thanks
      • JosWoolley's avatar
        JosWoolley
        Iron Contributor
        Apr 12, 2023

        Lauren_Hofstetter21 

         

        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

         

  • mtarler's avatar
    mtarler
    Silver Contributor
    Apr 12, 2023

    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_Hofstetter21's avatar
      Lauren_Hofstetter21
      Copper Contributor
      Apr 13, 2023
      You amaze me, I'm only self taught in Excel, and have learned a lot but this is much appreciated!
      • mtarler's avatar
        mtarler
        Silver Contributor
        Apr 13, 2023
        Lauren 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

Resources