Creating new rows from a minimum value and count

[ScruffyW1lf]

Hi, have a large number of records that includer a reference, a minimum value and a maximum value e.g. 0 (min) 800 (max). The minimum and maximum values vary per record but are all wholly divisible by 10. I wish to break down the record into a series of new records so that e.g. a record min= 0, max = 800 is now a series of records with the same reference as the original but 0-10,10-20, 20-30 until it reaches the max value - in this case 790-800. I have a count already set up against each original record so know how may "sub" records to expect. I am assumin this will need some iterative VBA or similar, but haven't a clue how to do it. Anyway care to teach this old dog?

 

[Georgiboy]

Perhaps the below will help:

Book1
	A	B	C	D	E	F	G
1	Reference	Min	Max	Count		Reference	Bracket
2	123	0	50	5		123	0-10
3	456	50	100	5		123	10-20
4	789	100	500	40		123	20-30
5						123	30-40
6						123	40-50
7						456	50-60
8						456	60-70
9						456	70-80
10						456	80-90
11						456	90-100
12						789	100-110
13						789	110-120
14						789	120-130
15						789	130-140
16						789	140-150
17						789	150-160
18						789	160-170
19						789	170-180
20						789	180-190
21						789	190-200
22						789	200-210
23						789	210-220
24						789	220-230
25						789	230-240
26						789	240-250
27						789	250-260
28						789	260-270
29						789	270-280
30						789	280-290
31						789	290-300
32						789	300-310
33						789	310-320
34						789	320-330
35						789	330-340
36						789	340-350
37						789	350-360
38						789	360-370
39						789	370-380
40						789	380-390
41						789	390-400
42						789	400-410
43						789	410-420
44						789	420-430
45						789	430-440
46						789	440-450
47						789	450-460
48						789	460-470
49						789	470-480
50						789	480-490
51						789	490-500
Sheet1

Cell Formulas
Range	Formula
F2:G51	F2	=LET(startRange,B2:B4,countRange,D2:D4,ref,A2:A4, rpt,TOCOL(IF(SEQUENCE(,MAX(countRange))<=countRange,ref,#N/A),3), cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange), rowSeq,SEQUENCE(SUM(countRange),1,0,1), totCount,SUM(countRange), modRow,MOD(rowSeq,totCount), series,MATCH(modRow,cumTotal), startValue,INDEX(startRange,series), subtract,INDEX(cumTotal,series), result,startValue+(rowSeq*10)-(subtract*10), HSTACK(rpt,result & "-" & result+10))
D2:D4	D2	=(C2-B2)/10
Dynamic array formulas.

If the data is very large then it might be better to wait for a VBA solution.

Inspiration came from the below link:
Incrementing multiple sequence of numbers in single excel column

 

[ScruffyW1lf]

Perhaps the below will help:

Book1
	A	B	C	D	E	F	G
1	Reference	Min	Max	Count		Reference	Bracket
2	123	0	50	5		123	0-10
3	456	50	100	5		123	10-20
4	789	100	500	40		123	20-30
5						123	30-40
6						123	40-50
7						456	50-60
8						456	60-70
9						456	70-80
10						456	80-90
11						456	90-100
12						789	100-110
13						789	110-120
14						789	120-130
15						789	130-140
16						789	140-150
17						789	150-160
18						789	160-170
19						789	170-180
20						789	180-190
21						789	190-200
22						789	200-210
23						789	210-220
24						789	220-230
25						789	230-240
26						789	240-250
27						789	250-260
28						789	260-270
29						789	270-280
30						789	280-290
31						789	290-300
32						789	300-310
33						789	310-320
34						789	320-330
35						789	330-340
36						789	340-350
37						789	350-360
38						789	360-370
39						789	370-380
40						789	380-390
41						789	390-400
42						789	400-410
43						789	410-420
44						789	420-430
45						789	430-440
46						789	440-450
47						789	450-460
48						789	460-470
49						789	470-480
50						789	480-490
51						789	490-500
Sheet1

Cell Formulas
Range	Formula
F2:G51	F2	=LET(startRange,B2:B4,countRange,D2:D4,ref,A2:A4, rpt,TOCOL(IF(SEQUENCE(,MAX(countRange))<=countRange,ref,#N/A),3), cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange), rowSeq,SEQUENCE(SUM(countRange),1,0,1), totCount,SUM(countRange), modRow,MOD(rowSeq,totCount), series,MATCH(modRow,cumTotal), startValue,INDEX(startRange,series), subtract,INDEX(cumTotal,series), result,startValue+(rowSeq*10)-(subtract*10), HSTACK(rpt,result & "-" & result+10))
D2:D4	D2	=(C2-B2)/10
Dynamic array formulas.

If the data is very large then it might be better to wait for a VBA solution.

Inspiration came from the below link:
Incrementing multiple sequence of numbers in single excel column

Wow! Didn't expect a solution as an equation but would much prefer it that way - although there are about 2000 (and counting) lines to convert which will likely result in considerably more records so maybe this might send my laptop into panic mode. Absolutely intriguing though and I will give it a try. The resultant range in column G is obviously text but the start value alone is far more useful to me and as a value, so I assume the HSTACK entry is reduced to (rpt,result) ?

 

[Georgiboy]

Yes, so the below would give you the brackets as numbers in seperate columns:

Book1
	A	B	C	D	E	F	G	H
1	Reference	Min	Max	Count		Reference	lBracket	uBracket
2	123	0	50	5		123	0	10
3	456	50	100	5		123	10	20
4	789	100	500	40		123	20	30
5						123	30	40
6						123	40	50
7						456	50	60
8						456	60	70
9						456	70	80
10						456	80	90
11						456	90	100
12						789	100	110
13						789	110	120
14						789	120	130
15						789	130	140
16						789	140	150
17						789	150	160
18						789	160	170
19						789	170	180
20						789	180	190
21						789	190	200
22						789	200	210
23						789	210	220
24						789	220	230
25						789	230	240
26						789	240	250
27						789	250	260
28						789	260	270
29						789	270	280
30						789	280	290
31						789	290	300
32						789	300	310
33						789	310	320
34						789	320	330
35						789	330	340
36						789	340	350
37						789	350	360
38						789	360	370
39						789	370	380
40						789	380	390
41						789	390	400
42						789	400	410
43						789	410	420
44						789	420	430
45						789	430	440
46						789	440	450
47						789	450	460
48						789	460	470
49						789	470	480
50						789	480	490
51						789	490	500
Sheet1

Cell Formulas
Range	Formula
F2:H51	F2	=LET(startRange,B2:B4,countRange,D2:D4,ref,A2:A4, rpt,TOCOL(IF(SEQUENCE(,MAX(countRange))<=countRange,ref,#N/A),3), cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange), rowSeq,SEQUENCE(SUM(countRange),1,0,1), totCount,SUM(countRange), modRow,MOD(rowSeq,totCount), series,MATCH(modRow,cumTotal), startValue,INDEX(startRange,series), subtract,INDEX(cumTotal,series), result,startValue+(rowSeq*10)-(subtract*10), HSTACK(rpt,result,result+10))
D2:D4	D2	=(C2-B2)/10
Dynamic array formulas.

 

[ScruffyW1lf]

Perhaps the below will help:

Book1
	A	B	C	D	E	F	G
1	Reference	Min	Max	Count		Reference	Bracket
2	123	0	50	5		123	0-10
3	456	50	100	5		123	10-20
4	789	100	500	40		123	20-30
5						123	30-40
6						123	40-50
7						456	50-60
8						456	60-70
9						456	70-80
10						456	80-90
11						456	90-100
12						789	100-110
13						789	110-120
14						789	120-130
15						789	130-140
16						789	140-150
17						789	150-160
18						789	160-170
19						789	170-180
20						789	180-190
21						789	190-200
22						789	200-210
23						789	210-220
24						789	220-230
25						789	230-240
26						789	240-250
27						789	250-260
28						789	260-270
29						789	270-280
30						789	280-290
31						789	290-300
32						789	300-310
33						789	310-320
34						789	320-330
35						789	330-340
36						789	340-350
37						789	350-360
38						789	360-370
39						789	370-380
40						789	380-390
41						789	390-400
42						789	400-410
43						789	410-420
44						789	420-430
45						789	430-440
46						789	440-450
47						789	450-460
48						789	460-470
49						789	470-480
50						789	480-490
51						789	490-500
Sheet1

Cell Formulas
Range	Formula
F2:G51	F2	=LET(startRange,B2:B4,countRange,D2:D4,ref,A2:A4, rpt,TOCOL(IF(SEQUENCE(,MAX(countRange))<=countRange,ref,#N/A),3), cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange), rowSeq,SEQUENCE(SUM(countRange),1,0,1), totCount,SUM(countRange), modRow,MOD(rowSeq,totCount), series,MATCH(modRow,cumTotal), startValue,INDEX(startRange,series), subtract,INDEX(cumTotal,series), result,startValue+(rowSeq*10)-(subtract*10), HSTACK(rpt,result & "-" & result+10))
D2:D4	D2	=(C2-B2)/10
Dynamic array formulas.

If the data is very large then it might be better to wait for a VBA solution.

Inspiration came from the below link:
Incrementing multiple sequence of numbers in single excel column

Well, I'm incredibly impressed and much appreciate your solution. I will certainly give this a go tomorrow morning. I guess it'd be wise for me to see whether a VBA solution is offered up too, just in case my laptop does fall over as it often does with excessively large amounts of data and equations.

 

[Georgiboy]

If you sum the count column that will tell you how many rows the formula will create.

 

[ScruffyW1lf]

If you sum the count column that will tell you how many rows the formula will create.

Yes, of course it will. 16643 expected so far from1286 base records. It could be a thumb-twiddling morning whilst calculating or not. Lots of coffee and feet up on the desk planned.

 

[ScruffyW1lf]

I've finally got around to applying the equation you provided - just noticed there is a #N/A embedded in line 2. Given that my first attempt with tweaks to the ranges are resulting in a #NAME? failure, I am assuming the #N/A is responsible?

 

[ScruffyW1lf]

Also, a stray "," in line 2 .....TOCOL(IF(SEQUENCE(,MAX.....

 

[Georgiboy]

The #N/A is suposed to be in the formula. It is used to weed out information we don't want. It basically creates an error that is then ignored due to the 3 on the end of the TOCOL function.
That "," is not stray, before the comma is rows, after the comma is columns. I have left rows blank as it will default to 1.

I have just tested the formula with data that amounts to 69,000 rows. This is with my data.

Do you have anything in the data that is different to the demo i made up?
Are you able to use the XL2BB addin to display some of the data you are working with?