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 purchase 1 golf ball, Column A would have 1, and Column B would have 1

If the next person purchases 3 golf balls, Column A would have 3, Column B  would have 2, 3, 4

If the next person purchases 5 golf balls, Column A would have 5, Column B would have 5, 6, 7, 8, 9

and so on. Is there a formula for this? Column B would be based on what is in Column A, and what is in the cell above it in Column B.

  • 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

        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.

Resources