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
    Apr 12, 2023

    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

Resources