Advanced Formula Challenge #3: Numbers From Hyphen-Separated List 19

Given the list in A1:A6, where some entries consist of a single number and some of a grouping of numbers (where e.g. 13-16 represents 13, 14, 15 and 16), the challenge this week is to come up with a single formula in D1 which, when copied down an arbitrary number of rows, produces a list of all individual, ungrouped numbers from the list in A1:A6, as here:


Picture1

The formula is to return a blank in rows beyond the expected number of returns. What’s more, this must be done via reference to a second formula, in C1, also to be derived and whose value is to equal the total expected number of non-blank entries to be returned in column D for any given dataset.

As such, the formula in D1 is to begin with:

=IF(ROWS($1:1)>$C$1,...

etc.

(We don’t do “lazy” IFERROR approaches here at EXCELXOR!)

Readers whose first idea might be to develop a solution which first tackles the string in A1, then the string in A2, and so on until the string in A6 might want to bear in mind that I only chose a range of six cells here for display purposes. What I mean by that is that your solution should be able to cope equally if the data range was not A1:A6 but e.g. A1:A100.

It can be assumed that none of the cells in the range A1:A6 are empty.

As an additional caveat this week, readers are asked, where a solution contains one or more clauses which occur more than once within the formula, and which are of a sizeable length, to store these clauses as Named Ranges and reference those in the formula instead. Furthermore, the range A1:A6 is to be referenced as the Named Range Range1 in any solution.

Best of luck! Solution next week!

19 comments

  1. ok…so….

    Name: first
    Refers to:

    =LEFT(SUBSTITUTE($A$1:$A$6,"-",REPT(" ",5)),5)

    Name: last
    Refers to:

    =RIGHT(SUBSTITUTE($A$1:$A$6,"-",REPT(" ",5)),5)

    5 is arbitrary…. you can use 10 or 15 if you want (represent length of number).

    In D1 you can put this formula and copy down (CSE formula):

    =IF(ROWS($D$1:$D1)>SUM(last-first+1),"",SMALL(IF(first+TRANSPOSE(ROW(INDIRECT("1:"&MAX(last-first)+1))-1)>--last,"",first+TRANSPOSE(ROW(INDIRECT("1:"&MAX(last-first)+1))-1)),ROWS($D$1:$D1)))

    Regards πŸ™‚

  2. Well.. I hope this was not a NON-ARRAY challenge.. and I have used IFERROR in this.. So.. I’m not sure if I get tagged as lazy for this πŸ˜€

    This is a single Array Formula.. and we would need to select the maximum number of cells first for the formula to work. For ex: if the maximum number in the range was 200, then we would need to select 200 cells first, with the formula in the first cell and then press CTRL+SHIFT+ENTER.

    Below is the formula:

    =ROW(INDIRECT(MIN(VALUE(IFERROR(MID($A$1:$A$6,1,FIND("-",$A$1:$A$6)-1),$A$1:$A$6)))&":"&MAX(VALUE(IFERROR(MID($A$1:$A$6,FIND("-",$A$1:$A$6)+1,LEN($A$1:$A$6)-FIND("-",$A$1:$A$6)),$A$1:$A$6)))))

    There is one limitation to this.. We cannot have 0 in the range..

    Cheers πŸ™‚

  3. The inverted Commas n the formula above is as per the Website style and this will result in an error in excel. Kindly have this changed to the normal excel style Inverted Comma in case you get an error

  4. @Calvin

    Thanks a lot for your solution!

    Can you just clarify what results you get? I entered it as a multi-cell array formula over a range of 21 vertical cells, and didn’t get the desired results (in fact, I got simply a list of integers from 1 to 21 inclusive).

    Many thanks

  5. Oops! I was under an impression that we need to create a list of Integer numbers.. Apologies πŸ™‚ I didn’t see the image clearly.. I’ll work on a new solution πŸ™‚

  6. @Calvin

    Ouch! I’m sorry, and you obviously put a lot of work into that solution as well!

    Still, there’s some nice technique in there, whatever the result, so I look forward to seeing more of your work in future πŸ™‚

    Cheers

  7. I don’t know if I’m too late or not.

    I came across this challenge and I just had to try.

    In Name Manger this DNR and two named formulae

    Named: Range1

    =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",20),Sheet1!$A:$A,1))

    Then the LEFTmost numbers in Range1
    Named formula: LeftVals

    =--TRIM(LEFT(SUBSTITUTE(Range1,"-",REPT(" ",20)),20))

    The RIGHTmost numbers in Range1
    Named formula: RightValse

    =--TRIM(RIGHT(SUBSTITUTE(Range1,"-",REPT(" ",20)),20))

    In helper cell C1

    =SUMPRODUCT(RightVals-LeftVals+1)

    Then in D1 array-entered filled down.

    =IF(ROWS($1:1)=TRANSPOSE(LeftVals))*(ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))<=TRANSPOSE(RightVals)),ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))),ROWS($1:1)),"")
  8. Hi Dave and welcome to the site!

    It’s never too late to submit an entry to one of my challenges – the more the merrier! πŸ™‚

    Before you did so, did you check out the results to this challenge?

    https://excelxor.com/2014/09/10/advanced-formula-challenge-3-results-and-discussion/

    I assume not, so as not to spoil the fun of having a go yourself.

    In any case, could you please just check your main formula? I get a syntax error, though this could be due to the post/comment editor here having mistyped your intended entry. If so, can you clarify what it should have been?

    Many thanks.

  9. Yes. The “Test” part of the IF function got somehow shortened. The whole formula should be

    =IF(ROWS($1:1)<=$C$1,"",TRANSPOSE(LeftVals))*(ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))<=TRANSPOSE(RightVals)),ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))),ROWS($1:1)),"")

    You wrote:

    “Before you did so, did you check out the results to this challenge?”

    and

    “I assume not, so as not to spoil the fun of having a go yourself.”

    That is correct. Before posting, however I did check to see if mine was a duplicate. It appeared Bill Szysz and I came up similar formulae and definitely the same approach. But there was enough difference I took a chance and posted just the same.

    By the way do you get syntax error, now?

  10. Thanks and apologies.

    I’ve corrected the initial IF statement, although I naturally still have a syntax error on the TRANSPOSE part.

    Can you check that the formula as rendered above matches yours precisely?

    Regards

  11. This is copied directly from my formula bar as I did in the previous post. I see now that parts are still missing … SMALL function in particular.

    I’ll try again.

    =IF(ROWS($1:1)=TRANSPOSE(LeftVals))*(ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))<=TRANSPOSE(RightVals)),ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))),ROWS($1:1)),"")

    I’ve checked and double checked this posted formula against what I have in the formula bar of my WB. It exactly matches, but I copied / pasted the last time as well. 😦

    If this fails is there an alternative?

    Thanks,
    Dave

  12. Upon further review a whole section of my formula is missing:

    SMALL(IF((ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))>=

    This just precedes the first TRANSPOSE.

    Don’t know what is happening.

  13. My 1/25/2016 20:41 post did the same thing.

    I cannot think of another way to do this.

    There are 77 characters missing in that formula.

  14. After receiving an email from Dave, I can confirm that the following is his intended main formula:

    =IF(ROWS($1:1)<=$C$1,SMALL(IF((ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))>=TRANSPOSE(LeftVals))*(ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))<=TRANSPOSE(RightVals)),ROW(INDEX($A:$A,MIN(LeftVals)):INDEX($A:$A,MAX(RightVals)))),ROWS($1:1)),"")
    
  15. @XOR LX

    Would you be able to provide a solution that does the reverse of this challenge?? I’m stuck here at work, and need to create a tool to build a hyphen separated list for me.

    Starting with the values from the image in Column D, can you build a list/grouping that returns what is shown in Column A?

    Thanks!

  16. Hi Kyle,

    Great question!

    I’m sure this can be improved upon, but for now, try:

    In C1, used for the count:

    =COUNT(MODE.MULT(IF(MMULT(COUNTIF(D$1:D$12,D$1:D$12-{1,-1}),{1;3})={0,3},D$1:D$12),D$1:D$12))

    In A1:

    =IF(ROWS($1:1)>C$1,"",INDEX(MODE.MULT(IF(MMULT(COUNTIF(D$1:D$12,D$1:D$12-{1,-1}),{1;3})={0,3},D$1:D$12),D$1:D$12)&REPT("-"&MODE.MULT(IF(MMULT(COUNTIF(D$1:D$12,D$1:D$12-{1,-1}),{1;3})={0,1},D$1:D$12),D$1:D$12),MMULT(COUNTIF(D$1:D$12,MODE.MULT(IF(MMULT(COUNTIF(D$1:D$12,D$1:D$12-{1,-1}),{1;3})={0,1},D$1:D$12),D$1:D$12)-{1,-1}),{1;3})0),ROWS($1:1)))

    and copied down.

    Hope that helps!

    Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s