Apr 12 2023 06:00 AM
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.
Apr 12 2023 06:41 AM
This is an example of what I need
Apr 12 2023 06:52 AM - edited Apr 12 2023 06:52 AM
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)))
)
)
Apr 12 2023 09:48 AM
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.
Apr 12 2023 10:59 AM
Apr 12 2023 11:29 AM
Solution
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
Apr 12 2023 11:48 AM - edited Apr 12 2023 11:52 AM
I don't understand how this fits in. Is this instead of the above counting or for certain conditions or something else? As for your counting let me note that counting using letters only SHOULD go: A, B, C, ... , Y, Z, AA, AB, AC, ... and NOT AA, BB, ...
IF that is OK then there is a simple trick in excel to use the ADDRESS function to have Excel convert for you as long as a max of XFD (i.e. 16384) is high enough. The trick is:
=SUBSTITUTE(ADDRESS(1,NNN,4),"1","")
Put any number (or reference to a number) in NNN and it will output the corresponding letters.
So w/r to the above equation here is it modified for letters:
=LET(
ζ,A2:INDEX(A:A,MATCH(7^89,A:A)),
BYROW(ζ,LAMBDA(ξ,
TEXTJOIN(",",,SUBSTITUTE(ADDRESS(1,SEQUENCE(ξ,,SUM(A2:ξ)-ξ+1),4),"1","")))
)
)
Apr 13 2023 07:59 AM
Apr 13 2023 08:26 AM
Apr 13 2023 08:33 AM
Apr 13 2023 08:53 AM
Apr 12 2023 11:29 AM
Solution
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