• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting list items from single cell containing items and ranges

larryf

New Member
Hi all...

I frequently have to take the contents of a cell that has a mix of individual 5-digit codes and ranges of 5-digit codes--separated by commas--and create a list (or vector) of all constituent codes. As an example, I might be given a single cell containing: 10000-10002, 15555, 27984-27986. The number of elements can vary between input cells, but the end of any range is always the full five digits. In other words, the range 10000-10002 is always specified that way, and never as 10000-2. The outptut I'd like would be a list, like this:
10000
10001
10002
15555
27984
27985
27986

Is there some clever way to accomplish this? I spend an inordinate amount of time retyping these.

I appreciate any help you can provide!
 

Attachments

bosco_yip

Excel Ninja
Try this formula solution with helper column

1] In "Helper 1" C7, formula copied down :
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A$3,",","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"")

2] In "Helper 2" D7, formula copied across and down :
=IF((LEN($C7)=5)*(SUM($C7:C7)=$C7),$C7,IF(COLUMN(A1)<=-IMREAL(IMDIV($C7&"i","1+i"))*2+1,IMREAL($C7&"i")+COLUMN(A1)-1,""))

3] In "Result" A7, CSE (Ctrl+Shift+Enter) formula copied down :
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($D$7:$M$12<>"",$D$7:$M$12,""))&"</b></a>","//b["&ROW(A1)&"]"),"")

74235
 

Attachments

Peter Bartholomew

Well-Known Member
An amazing tour-de-force; all achieved with legacy Excel!
The fluency with which data is extracted with xml, in combination with complex number processing shows complete mastery of the obscure!
 

Peter Bartholomew

Well-Known Member
It doesn't get that much easier using modern Excel,
74237
... and I have still to replace two of Charles Williams's FastExcel functions (SPLIT.TEXT and ACCUMULATE) with Lambda functions. Perhaps the only good thing to be said about the formula is that the output is a single dynamic array and no helper ranges are required.
 

larryf

New Member
Try this formula solution with helper column

1] In "Helper 1" C7, formula copied down :
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A$3,",","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"")

2] In "Helper 2" D7, formula copied across and down :
=IF((LEN($C7)=5)*(SUM($C7:C7)=$C7),$C7,IF(COLUMN(A1)<=-IMREAL(IMDIV($C7&"i","1+i"))*2+1,IMREAL($C7&"i")+COLUMN(A1)-1,""))

3] In "Result" A7, CSE (Ctrl+Shift+Enter) formula copied down :
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($D$7:$M$12<>"",$D$7:$M$12,""))&"</b></a>","//b["&ROW(A1)&"]"),"")

View attachment 74235
I am in awe and very grateful! This solution will materially improve my productivity (and eliminate mindless re-typing and the errors associated with it). Thanks so much for your help!
 

Peter Bartholomew

Well-Known Member
Note. This is of interest only if you have access to Excel 365 insider beta, which cuts it down a tad.
Rather than relying on Charles Williams's excellent FastExcel functions, I replaced them with a couple of λ-functions.
The first, SPLITXMLλ(list, comma), is lifted directly from @bosco_yip's solution
Code:
= LAMBDA(string,sep,
    LET(
      XML, "<list><item>"&
         SUBSTITUTE(string,sep,"</item><item>")&
         "</item></list>",
      XPath, "//item",
      FILTERXML(XML,XPath))
   )
(just a bit more verbose).
For the second, ACCUMULATEλ(terms), I chose to use matrix multiplication rather than recursion.
Code:
= LAMBDA(v,
    LET(
      n, COUNT(v),
      k, SEQUENCE(n),
      M, SIGN(TRANSPOSE(k)<k),
      1+MMULT(M, v))
   )
The formula itself now reads
Code:
= LET(
    comma, ",", hyphen, "-",
    ranges, SPLITXMLλ(list, comma),
    splits, IFERROR(SEARCH(hyphen, ranges), 0),
    start,  IF(splits, LEFT(ranges, splits-1 ), ranges),
    end,    IF(splits, MID(ranges, splits+1, 5), start),
    terms,  1 + (end - start),
    ptr,    ACCUMULATEλ(terms),
    k,      SEQUENCE(SUM(terms)),
    base,   XLOOKUP(k, ptr, start,,-1),
    offset, k - XLOOKUP(k, ptr, ptr,,-1),
  base+offset )
Perhaps there should be a prize for the smallest problem that demands the largest solution!
 

Attachments

larryf

New Member
Note. This is of interest only if you have access to Excel 365 insider beta, which cuts it down a tad.
Rather than relying on Charles Williams's excellent FastExcel functions, I replaced them with a couple of λ-functions.
The first, SPLITXMLλ(list, comma), is lifted directly from @bosco_yip's solution
Code:
= LAMBDA(string,sep,
    LET(
      XML, "<list><item>"&
         SUBSTITUTE(string,sep,"</item><item>")&
         "</item></list>",
      XPath, "//item",
      FILTERXML(XML,XPath))
   )
(just a bit more verbose).
For the second, ACCUMULATEλ(terms), I chose to use matrix multiplication rather than recursion.
Code:
= LAMBDA(v,
    LET(
      n, COUNT(v),
      k, SEQUENCE(n),
      M, SIGN(TRANSPOSE(k)<k),
      1+MMULT(M, v))
   )
The formula itself now reads
Code:
= LET(
    comma, ",", hyphen, "-",
    ranges, SPLITXMLλ(list, comma),
    splits, IFERROR(SEARCH(hyphen, ranges), 0),
    start,  IF(splits, LEFT(ranges, splits-1 ), ranges),
    end,    IF(splits, MID(ranges, splits+1, 5), start),
    terms,  1 + (end - start),
    ptr,    ACCUMULATEλ(terms),
    k,      SEQUENCE(SUM(terms)),
    base,   XLOOKUP(k, ptr, start,,-1),
    offset, k - XLOOKUP(k, ptr, ptr,,-1),
  base+offset )
Perhaps there should be a prize for the smallest problem that demands the largest solution!
I've been waiting for Microsoft to roll out Lambdas to the rest of us, but not with any actual project or use in mind. Now I've got a real reason to look forward to expanded availability. I honestly didn't know if this was a problem that could be solved with Excel, but you and bosco_yip each solved it in 24 hours. My hat's off to both of you!
 

larryf

New Member
Please try

Code:
=LET(a,SUBSTITUTE(FILTERXML("<x><m>"&SUBSTITUTE(A3,",","</m><m>")&"</m></x>","//m"),"-",REPT(" ",9)),
l,--LEFT(a,9),r,--RIGHT(a,9),s,l+SEQUENCE(,MAX(r-l)+1,0),b,IF(s<=r,s),MODE.MULT(b,b))
I tried it and it works great! Amazing solution. Thanks for your help!
 
Top