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

     

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

       

Resources