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.

Formulas and Functions

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

Lauren_Hofstetter21
Copper Contributor
Apr 12, 2023
Lauren_Hofstetter21
This is an example of what I need
- 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
    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.

Forum Discussion

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

Share

Resources