Turn number into comma separated series

Darth Emphatic

New Member
Joined
Jan 31, 2010
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Not sure if this is possible in excel, which is my preferred place to do it, but I could do in another way if needed. I found another thread on the forums with a similar question, that someone then suggests viewing another forum thread, but when I click on that link, it just routes to the message board home page for me:


In a a set of rows, I have numbers like below:

1
4
3
2

What I want to do is convert those into comma delimited sequences starting at the last number in the previous row. The above would look like:

1
2,3,4,5
6,7,8
9,10
 
I think I spoke too soon. I'll update my profile (I am on office 2016). I also tried the same formula in google sheets. Same error:
1632947137422.png

Mr Excel Example.xlsx
AB
11#NAME?
24#NAME?
33#NAME?
42#NAME?
Example Data
Cell Formulas
RangeFormula
B1:B4B1=LET(s,A1,dif,SUM($A$1:A1)-s+1,TEXTJOIN(",",1,SEQUENCE(s,,dif)))


Got the same error with the
Excel Formula:
=MID(CONCAT(SEQUENCE(A1,1,SUM($A$1:A1)-A1+1)&","),1,LEN(CONCAT(SEQUENCE(A1,1,SUM($A$1:A1)-A1+1)&","))-1)
formula. Is it the excel version I am on?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
No, this formula should work in Office 2016. It Google sheets seems to part formulas differently. For example, I put the formula in Google Sheets, and it's saying that "there's only one argument in CONCAT but needs two" (rephrased). It is therefore important to mention Google Sheets (and not Excel) in situations like this, where advanced formulas are needed to create a pattern (if you intend for it to work in Google Sheets).
 
Upvote 0
No, this formula should work in Office 2016.
None of them will work in 2016. They both contain functions that only exist in 365 along with some that only exist in 2019/365
 
Upvote 0
Here is another way using a VBA macro...
VBA Code:
Sub DistributeNumbers()
  Dim N As Long, R As Long, LR As Long, Last As Long, Arr As Variant
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  Arr = Evaluate("ROW(1:" & Application.Sum(Range("A1:A" & LR)) & ")")
  For R = 1 To LR
    Cells(R, "B") = Join(Application.Transpose(Application.Index(Arr, Evaluate("ROW(" & N + 1 & ":" & N + Cells(R, "A").Value & ")"))), ",")
    N = Evaluate("SUM(A1:A" & R & ")")
  Next
End Sub
 
Upvote 0
Sounds like it's time to upgrade :)
Well, not just because of this. Turns out Google Sheets has JOIN(). This makes the formula much shorter (but you probably only can use it in Google Sheets).
Excel Formula:
=JOIN(",",SEQUENCE(A1,1,SUM($A$1:A1)-A1+1))
 
Upvote 0
Nailed it. Thank you for that!
I presume you are referring to cmowla's post with the formula using the Google Sheets' JOIN function. I'm curious... why did you asked a Google Sheets question in an Excel forum? Doesn't Google Sheets have its own forum with volunteers who are familiar with that product's functionality?
 
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,138
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top