Good day,
I am struggling to give a numbering to comma-separated numbers from the column A.
The logic is very simple. The numbers should be sequentially numbered by their respective q-ty in each cell. For example:
100 - 1
100, 200 - 1, 2
200, 300, 400, 500 - 1, 2, 3, 4
500 -1 and etc.
Desired output in column B.
Perhaps the formula may or may not be simple, I just can't figure it out.
Hi,
Try These formula. Also attached the sheet for reference.
In cell B1 and drag down
=TEXTJOIN(",",TRUE,C1:V1)
In Cell C1 drag acrross and down
=IF(IFERROR(FILTERXML("<A><B>"&SUBSTITUTE($A1,",","</B><B>")&"</B></A>","//B["&COLUMNS($B1:B1)&"]"),"")<>"",COLUMN()-2,"")
Last edited by HariArvi; 08-10-2022 at 08:43 AM.
Hi, the numbers should be displayed in the column. Pls. see the screenshot.
P.S. Neither 2013 nor 2016 Excel versions have a TEXTJOIN function
Hi,
Try this formula.
In cell B1 and drag
=IF(ISERROR(FIND(",",A1))=TRUE,1,LEN(A1)-LEN(SUBSTITUTE(A1,",","")))
Ignore the previous post. I assumed you wanted the sequence to be created.
Please try at B1
=IFERROR(MOD(AGGREGATE(15,6,ROW($A$1:$A$4)*100+COLUMN($A$1:$Z$1)/(LEN($A$1:$A$4&0)-LEN(SUBSTITUTE($A$1:$A$4,",",))>=COLUMN($A$1:$Z$1)),ROWS(B$1:B1)),100),"")
Sadly, no. Formula fails as soon as the range referenced by the formula ends. Please refer to the column B with correct values.
Bo_Ry, Yes, it works like a charm! Who could have doubted it? Thank you!
Problem solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks