Sequencing numbers

[clandestino800]

Hi all,

I have been trying create a list of numbers based on the values of two columns (J and K).
e.g 10-39 should vertically list all numbers from 10 to 39 from L1 to L39 (10, 11, 12, etc).

I tried using SEQUENCE() but that doesn't seem to be the right approach.


.

Thanks in advance for any suggestions!

RB

 

[felixstraube]

It's not quite clear what you want. In post 1 you say you want the sequence from 10 to 39 but in the image the number go from 31 to 39.
Do you want a sequence of numbers for every row of data you have? Or for every group of rows?
Could you explain in more detail and show us possible scenarios and expected results?

 

[clandestino800]

Sure, I am allocatiing values to weeks that may change (Start Week / End Week) using the start and end date as reference.
The secuence should be able to change in order to allocate the values equally among the weeks. I'm not sure this is the correct approach, though.

Start Week	End Week	Start Week Nr.	End Week Nr.
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
04.03.2024​	27.09.2024​	10​	39​
29.01.2024​	01.03.2024​	5​	9​
29.01.2024​	01.03.2024​	5​	9​
29.01.2024​	01.03.2024​	5​	9​
29.01.2024​	01.03.2024​	5​	9​
29.01.2024​	01.03.2024​	5​	9​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	28.06.2024​	10​	26​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​
04.03.2024​	30.08.2024​	10​	35​

 

[Fluff]

As Scott asked, could you have two different blocks with the same start & end week?

 

[clandestino800]

Sure, i can do that

 

[Fluff]

How about

Fluff.xlsm
	J	K	L
1	Start Week Nr.	End Week Nr.
2	10	39	10
3	10	39	11
4	10	39	12
5	10	39	13
6	10	39	14
7	10	39	15
8	10	39	16
9	10	39	17
10	10	39	18
11	10	39	19
12	10	39	20
13	10	39	21
14	10	39	22
15	10	39	23
16	10	39	24
17	10	39	25
18	10	39	26
19	10	39	27
20	10	39	28
21	10	39	29
22	10	39	30
23	10	39	31
24	10	39	32
25	10	39	33
26	10	39	34
27	10	39	35
28	10	39	36
29	10	39	37
30	10	39	38
31	10	39	39
32	5	9	5
33	5	9	6
34	5	9	7
35	5	9	8
36	5	9	9
37	10	26	10
38	10	26	11
39	10	26	12
40	10	26	13
41	10	26	14
42	10	26	15
43	10	26	16
44	10	26	17
45	10	26	18
46	10	26	19
47	10	26	20
48	10	26	21
49	10	26	22
50	10	26	23
51	10	26	24
52	10	26	25
53	10	26	26
54	10	35	10
55	10	35	11
Data

Cell Formulas
Range	Formula
L2:L55	L2	=IF(OR(J2<>J1,K2<>K1),J2,L1+1)

 

[Sanjeev1976]

May be an option :

Book2
	J	K	L	M	N	O
1	10	42	10		10	42
2	10	42	11		5	9
3	10	42	12		10	26
4	10	42	13
5	10	42	14
6	10	42	15
7	10	42	16
8	10	42	17
9	10	42	18
10	10	42	19
11	10	42	20
12	10	42	21
13	10	42	22
14	10	42	23
15	10	42	24
16	10	42	25
17	10	42	26
18	10	42	27
19	10	42	28
20	10	42	29
21	10	42	30
22	10	42	31
23	10	42	32
24	10	42	33
25	10	42	34
26	10	42	35
27	10	42	36
28	10	42	37
29	10	42	38
30	10	42	39
31	10	42	40
32	10	42	41
33	10	42	42
34	5	9	5
35	5	9	6
36	5	9	7
37	5	9	8
38	5	9	9
39	10	26	10
40	10	26	11
41	10	26	12
42	10	26	13
43	10	26	14
44	10	26	15
45	10	26	16
46	10	26	17
47	10	26	18
48	10	26	19
49	10	26	20
50	10	26	21
51	10	26	22
52	10	26	23
53	10	26	24
54	10	26	25
55	10	26	26
Sheet1

Cell Formulas
Range	Formula
L1:L55	L1	=VSTACK(SEQUENCE(O1-N1+1,,N1,1),SEQUENCE(O2-N2+1,,N2,1),SEQUENCE(O3-N3+1,,N3,1))
N1:O3	N1	=UNIQUE(J1:K42)
Dynamic array formulas.

 

[Fluff]

That only works for 3 different sets of numbers & does not take into account the same numbers possibly being repeated.

 

[clandestino800]

Dear all,

thank you very much for your help!
The solution suggested by fluff is doing what I needed.

Regards
Randall