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?

 

[ScruffyW1lf]

OK, Sadly I am unable to use XL2BB. Instead I tried replicating your example in a blank/unsullied workbook. - created exactly the same as per the example and copied/pasted the equation to F2. The same #NAME? error popped up. Makes me wonder whether I have total functionality.

 

[Georgiboy]

I see, if it is a #NAME error then one of the functions that has been used in the formula may not be available to you.

An easy way to check is to type an = in a cell and the start to type the functions below to see if you have them or not. I suspect one of the below, in the most likely order:
TOCOL
SEQUENCE
HSTACK

 

[Georgiboy]

If it is TOCOL that you don't have then maybe the below would work for you:

Book1
	A	B	C	D	E	F	G	H
1	Reference	Min	Max	Count		Reference	lBracket	uBracket
2	123	0	100	10		123	0	10
3	456	0	200	20		123	10	20
4	789	0	300	30		123	20	30
5						123	30	40
6						123	40	50
7						123	50	60
8						123	60	70
9						123	70	80
10						123	80	90
11						123	90	100
12						456	0	10
13						456	10	20
14						456	20	30
15						456	30	40
16						456	40	50
17						456	50	60
18						456	60	70
19						456	70	80
20						456	80	90
21						456	90	100
22						456	100	110
23						456	110	120
24						456	120	130
25						456	130	140
26						456	140	150
27						456	150	160
28						456	160	170
29						456	170	180
30						456	180	190
31						456	190	200
32						789	0	10
33						789	10	20
34						789	20	30
35						789	30	40
36						789	40	50
37						789	50	60
38						789	60	70
39						789	70	80
40						789	80	90
41						789	90	100
42						789	100	110
43						789	110	120
44						789	120	130
45						789	130	140
46						789	140	150
47						789	150	160
48						789	160	170
49						789	170	180
50						789	180	190
51						789	190	200
52						789	200	210
53						789	210	220
54						789	220	230
55						789	230	240
56						789	240	250
57						789	250	260
58						789	260	270
59						789	270	280
60						789	280	290
61						789	290	300
Sheet1

Cell Formulas
Range	Formula
F2:H61	F2	=LET(startRange,B2:B4,countRange,D2:D4,ref,A2:A4, rpt,XLOOKUP(SEQUENCE(SUM(countRange)),SCAN(0,countRange,LAMBDA(a,b,a+b)),ref,,1), 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))
Dynamic array formulas.

 

[ScruffyW1lf]

Seems my last has not appeared. Try again. No, sadly I cannot use XL2BB. However, I set up your example in a new unsullied workbook - copying across the formula you provided. The same #NAME? was the outcome. wondering now whether I have the functionality to make the equation a success.

 

[ScruffyW1lf]

It did and you replied. Refresh was a bit too slow for me. I actually tried all three, TOCOL, SEQUENCE and HSTACK. None of them were offered in the drop down list of functions as I was typing them out, so at least I've learned something.... I don't have everything when I thought I had.

 

[Fluff]

What is the build/version of your 365?

 

[ScruffyW1lf]

Right, I have two works laptops. Whilst waiting for my main one to boot up, I used the other one and called up the Excel book containing the supplied data and formula. This laptop had a version that recognised all the functions, so I continued with it and successfully ran the formula!! Success. Albeit I had to remove a rake of "@" in the formula that was not there on the misbehaving laptop. So whilst success has been achieved (it ran really quickly), I now have a bone to pick with Corporate IT. Many thanks for this, the formula will definitely be used many times again in the future.

 

[Georgiboy]

You are welcome, glad you got it working.