How to separate out a dynamic number range

[Enigmafish14]

I have a range of numbers that are not completely sequential and I'd like to separate them out into their individual numbers. In cell A1 I have displaying "1-30" and then in cell A2 I have "50-72" and A3 "100-105", et cetera. I 'd like to have cell B1 through B30 display 1 through 30 (1 in B1, 2 in B2, 3 in B3...) respectively, and then cell B31 through B53 would have 50 through 72.

I need to create a formula that can dynamically pick up the last number after the "-" so that it can work for any number range of any length. I've tried using left and right but that doesn't help when moving from the 10's digits to 100's digits.

[e4excel]

Enigma,

This formula would help you get any numbers from the LEFT or RIGHT
[ =RIGHT(A1,FIND("-",A1,1)) ]

The FIND function would give you the number from Left or Right..

If you feel that the help offered was helpful please rate me by clicking the scales option on the right on the Task Bar.


Warm Regards
e4excel

[Enigmafish14]

Cool, thank you for that.

How do I get the formula to recognize that Cell B31 will need to be "50" as opposed to "31" based on the ranges in the A column?

[e4excel]

Cool, thank you for that.

Your Welcome!

One more cool formula
[ =SUBSTITUTE(LEFT(A1,FIND("-",A1,1)),"-"," ",1) ]

to give you the value on the RIGHT OR LEFT.

How do I get the formula to recognize that Cell B31 will need to be "50" as opposed to "31" based on the ranges in the A column?

This is slightly confusing in the absence of a pure logic..
The Series is not with a constant difference..

Usually, the ROW function can be used to type the Series but it will fall flat after 30 ...Let me come back on this...!

[e4excel]

Hi Enigma,

Brother, I tried hard but could not come up with the solution for you sorry...
I came with a lot of logics but due to the uneven pattern nothing can remain DYnamic...

Fi you have something more to add to that which can help arrive at a solution then please post..

Bye..

WIsh could help you.

[JBeaucaire]

Ok, I got this working, but it requires that you create a "chart" off to the side out of your entries in the A column. There are formulas in the chart so the chart will create itself, up to 10 rows worth of "ranges" in column A.

Then, the B Column requires 2 formulas. The first is simply a "grab the first number off the chart" formula.

=F1

The second is where all the heavy lifting is going on. It checks the value above it and finds it in the chart, if it matches the end of one range, it starts the next range. If not, it increments the value above. If it's reached the end of the list or encounters an error, it quits the numbering.

=IF(B1="","",IF(OR(IF(B1<LOOKUP(B1,$F$1:$F$10,$G$1:$G$10),B1+1,LOOKUP(B1-1,$F$1:$F$10,$F$2:$F$11))=0,ISERROR(LOOKUP(B1,$F$1:$F$10,$G$1:$G$10))),"",IF(B1<LOOKUP(B1,$F$1:$F$10,$G$1:$G$10),B1+1,LOOKUP(B1-1,$F$1:$F$10,$F$2:$F$11))))

Copy that second formula down as far as you need to and it will start returning blank cells when it reaches the end of the chart's ranges.

Hope this works for you.

[JBeaucaire]

Oops, forgot to mention, I installed a UDF called IFERROR into the sheet, you'll need to install it in yours as well for the formulas in F and G to work.

Please Login or Register  to view this content.

Open your sheet
Press Alt-F11
Click Insert > Module
Paste in code above
Alt-F11 to close VBEditor
Save sheet.

Now these two formulas from F1 and G1 will function:

=iferror(LEFT(A1,LEN(A1)-(LEN(G1)+1))*1,"")
=iferror(RIGHT(A1,LEN(A1)-FIND("-",A1))*1,"")

======
NOTE: If you're using Excel 2007, IFERROR is already a function you can use, the UDF is unnecessary.

For Excel 2003, you can SKIP installing the UDF above, but the formulas in F1 and G1 would have to change to:

=IF(ISERROR(LEFT(A1,LEN(A1)-(LEN(G1)+1))*1),"",LEFT(A1,LEN(A1)-(LEN(G1)+1))*1)
=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND("-",A1))*1),"",RIGHT(A1,LEN(A1)-FIND("-",A1))*1)

[DonkeyOte]

As much as it pains me to say (being a lover of formulae!) I think a sub routine would be preferable in this instance (only my opinion mind)

Please Login or Register  to view this content.

You would invoke the above, select range containing the bands (assumes x-y format) and the routine will then populate column B accordingly (rows 2 onwards)

[JBeaucaire]

Yep, great stuff...but the OP's original post asked for a dynamic answer, which is why I too prefer a formulaic answer.

Can that nifty macro be made to self-activate, automate, be truly dynamic?

[DonkeyOte]

You could invoke via a Worksheet_Change of Intersect A:A if that's you mean.... as I said I appreciate the OP wanted a formula but given this is a forum where advice is to be offered I took the big leap of faith and offered mine (FWIW)

(I guess my point would be if I requested a macro to take values in range A1:A10 and add them to their corresponding values in B1:B10 and place the results in C1:C10 would you give me a subroutine or perhaps suggest I use C1: =A1+B1 ?)

[Spellbound]

Hi all

Have been watching this thread with interest as it may help with an idea for my project.

I managed to make the code run as a Macro but I would need it to work automatically; probably as a worksheet change event. However, as my knowledge of VBA is zilch, I would appreciate the revised code to make it work as a worksheet change event.

The other problem is that I would need the Column B listing to start from Row 1 not Row 2 as this example but could not see where to make this change.

TIA ...spellbound

[DonkeyOte]

Not particularly elegant but you could use something along the lines of:

Please Login or Register  to view this content.

Such that whenever any new band is inserted within A1:A10 Column B listing updates automatically in accordance with the revised bands present in that range. I wouldn't argue it's the "finished article" but should hopefully point you in the right direction if nothing else.

[Enigmafish14]

Hi, I prefer Jbeaucaire's formula approach but the downside to that is that the ranges have to be in number order which can be a headache when working with inventories. Donkeyote's macro works great because it doesn't need the ranges to be in order, but I am not too familiar with VBA.

I have a question about the macro. Is there a way to incorporate a typed function into it that will allow me to create another column that has the prices for each item also? I've attached a file with my example. Column F is the formula I'd like to use.

[DonkeyOte]

It's unclear to me at least as to whether or not the table in C:D exists in reality... assuming it does... such that values to be listed in E and formulae results in F then the following should work:

Please Login or Register  to view this content.

Per my response to Spellbound's question the above can be made into a Worksheet_Change event such that the list updates dynamically as you alter the bands etc...

[Enigmafish14]

Thanks a lot DonkeyOte! This macro works perfectly and I learned "isna" and "match" in the process.

The table in C:D was a real table based on formulas but the "vlookup" function in column F wasn't reading the values in the table so I had to cut and paste the values.

Is there a way to get a lookup type function to recognize the values of another function?

[JBeaucaire]

Lookup functions read the "values" in the tables they are referencing, not the formulas. If you don't believe that is occurring, then the problem will be in the structure of the LOOKUP formula. Post the lookup and possibly a sample of the data and we can help you spot the error in the formula.

[Enigmafish14]

The yellow cell is the lookup function that is seaching for the orange cell in column B. I think the reason that the lookup up function is not working has to do with the formatting somehow. When I paste the values in column B back to column B , the lookup works.

Why doesn't it work when the formulas in column B are present?

[JBeaucaire]

Couple of problems. First, the data in column B is string data. It looks like numbers but it's not. So wrap the formula in an INT() function to turn it back into a number.

=INT(LEFT(A2,FIND("-",A2)-1))
--or
=LEFT(A2,FIND("-",A2)-1)*1
...and copy down.

That will make the formulas in F workable, but there's an error there, too.

=IF(ISERROR(VLOOKUP(E2,$B$2:$D$30,2,FALSE)),$F$1,VLOOKUP(E2,$B$2:$D$30,2,FALSE))

Copy that down.

[DonkeyOte]

I agree with JB re: coercing column B to number.
(the other option would be to coerce the lookup value to text, eg VLOOKUP(E2&""... but adjusting B is the more logical solution)

However, JB, the VLOOKUP is in fact correct that is to say F1 reference should be relative and not absolute as you advise.

The formula has been setup such that as the formula is copied down should the VLOOKUP return an error simply return the prior row value, this is correct... ie the first number in a given band will return a valid result thereafter until the next band is reached all other values will return an error from the VLOOKUP, those entries should simply use the prior value given they reside in the same band.

That said, EngimaFish14, I myself remain adamant that you should be using ISNA(MATCH over ISERROR(VLOOKUP in your initial validity test ... there is no justification in this instance to use the latter over the former.

[JBeaucaire]

You lost me on the $F$1 thing. If the formula results in an error, he wants the words "Lookup Description" to result. If you don't make the F1 absolute before you copy it down, then row 3 returns the value from F2 instead.

Since fixing the string data in B, the row2 results to a non-error and he gets an answer in F2 of "LONG REACH COMPETITOR <291/2" and then the whole column turns to that since all the rest return error values. That can't be right? All the others should be returning the "Lookup Description" message, so $F$1 has to be locked in prior to copying that formula down.
===========

I'm interested in hearing more on justification. I understand there are multiple ways to accomplish the same task, so why so adamant that ISERROR(VLOOKUP() needs justification? ISNA(MATCH() is significantly better in what way, other than it works, too?

I'm interested to learn, online text sometimes reads as sarcastic, and I'm not being at all. What in your experience makes the choice so absolute?

[DonkeyOte]

LOOKUP_DESCRIPTION is just a header... the reference is definitely relative...

As you say if you fix the VLOOKUP such that the first record returns a result and you use F1 as relative before copying down the remaining rows in F use that same result also... this is 100% correct as all the values are in the same band as the first value... eg:

24402 will generate a VLOOKUP error as it does not reside in B, however, 24402 is in the same band as 24401 (24401-24429) so it should use the same lookup result as 24401 (ie the prior cell value)... when the value in E moves to the next band, eg 24501 the result in F will change accordingly and will repeat for all values in that same band etc etc...

As for ISNA(MATCH over ISERROR(VLOOKUP ... 2 main reasons:

1 - MATCH has less dependencies (you need only refer to one column)
2 - ISNA is more specific, other errors will not be "covered" up

I would only ever advocate ISERROR(VLOOKUP it it's possible for the critera to be found in the lookup range but for the resulting value itself to be an error value... ie errors in lookup range...

If you're only checking for the existence of the criteria in the left most column (as is the case here) you should use ISNA(MATCH( ... or ISNUMBER(MATCH if you want to work in the opposite direction.

[JBeaucaire]

Oh man, I missed the "band" thing altogether, and mostly because the chart is not laid out properly for that. When you use VLOOKUP and "fuzzy" matches, the data is supposed to be sorted by the searched/indexed column and the final parameter is TRUE, not FALSE.

A VLOOKUP the has an ISERROR check implied the FALSE was the right parameter, else there should be virtually no errors from the VLOOKUP at all. The TRUE option let's the result round down to the indexed "beginning of the range/band" properly.

Ugh.

So, the VLOOKUP should actually be OK without an error check:

=VLOOKUP(E2,$B$2:$D$31,2,TRUE)

--or if the blank row N/A stuff is undesirable, a check like suggested originally above:

=IF(E2<12314,"",VLOOKUP(E2,$B$2:$D$31,2,TRUE)

The same should be done in the pricing column.

[DonkeyOte]

No, not so, per the OP (post # 13) the bands will not necessarily be listed in Ascending order (inventory references) so the VLOOKUP as presented was correct and should be left as FALSE with prior cell reference as relative.

[Enigmafish14]

JB: I did leave the reference to F1 relative intentionally as DO explained. thank you for the info on the string value in B.

DO: Thank you for your help with the macro. But the ISNA and MATCH is not working for me in this new spreadsheet. Overall the whole ISNA and MATCH thing is over my head. But thank you.