Forum Discussion

Lauren_Hofstetter21's avatar
Lauren_Hofstetter21
Copper Contributor
Apr 12, 2023
Solved

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
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

     

Resources