Ranges

[meganm111]

Hi,

I'm looking to fill the numbers in these ranges. For example, I would want 350 in E2, 351, in F2, and 352 in G2. The next row would be 354 in E3, 355 in F3 and so on all the way to 359. I have 10K+ rows like this. As you can see, each range may be a different size. Let me know if you need additional info. Thank you

 

[hajiali]

If all values in Column D are as what's in the picture try this"

Book2
	E	F	G
2	350	351	352
Sheet2

Cell Formulas
Range	Formula
E2	E2	=LEFT(D2,3)
F2	F2	=IF(LEN(D2)>3,E2+1,"")
G2	G2	=IF(LEN(D2)>3,E2+2,"")

drag formula down

 

[meganm111]

If all values in Column D are as what's in the picture try this"

Book2
	E	F	G
2	350	351	352
Sheet2

Cell Formulas
Range	Formula
E2	E2	=LEFT(D2,3)
F2	F2	=IF(LEN(D2)>3,E2+1,"")
G2	G2	=IF(LEN(D2)>3,E2+2,"")

drag formula down

Thank you that gets me closer, but the next row is a range from 354-359. If I drag down the formula, I believe your solution would only work on ranges that are of the same length.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this copied down column E

21 02 16.xlsm
	D	E	F	G	H	I	J	K
1
2	350-352	350	351	352
3	354-359	354	355	356	357	358	359
4	360-361	360	361
5	362	362
6	363	363
7	364-366	364	365	366
8	367	367
Ranges

Cell Formulas
Range	Formula
E2:G2,E7:G7,E5:E6,E8,E4:F4,E3:J3	E2	=IFERROR(SEQUENCE(,-MID(D2,FIND("-",D2),20)-LEFT(D2,FIND("-",D2)-1)+1,LEFT(D2,FIND("-",D2)-1)),D2)
Dynamic array formulas.

 

[hajiali]

Not sure how many columns you will end up using but here I have up to column L:

Book2
	D	E	F	G	H	I	J	K	L
2	350-352	350	351	352	353
3	354-359	354	355	356	357	358	359
4	360-361	360	361
5	362	362
6	363	363
7	364-366	364	365	366
8	367	367
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(VALUE(LEFT(D2,3)),"")
F2:F8	F2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+1,"",E2+1),"")
G2:G8	G2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+2,"",E2+2),"")
H2:H8	H2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+3,"",E2+3),"")
I2:I8	I2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+4,"",E2+4),"")
J2:J8	J2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+5,"",E2+5),"")
K2:K8	K2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+6,"",E2+6),"")
L2:L8	L2	=IFERROR(IF(VALUE(LEFT(D3,3))<=E2+7,"",E2+7),"")

but for each column over just add 1 number or if you like use a helper column and drag down

 

[Norie]

If you are using Excel365 take a look at the SEQUENCE function.

 

[Peter_SSs]

If you are using Excel365 take a look at the SEQUENCE function.

Like in post #4?