Need help achieving an end.

[BEDE]

Hello,
I need some help achiving an end but can't wrap my head around this one.

Some background: The unique column is just that. A column of all the unique values from column C. However, it is too big, I just left it out. The other two columns are spilled, they are the ranges for the unique. For example, the first unique 0.174 starts at cell 1 it's range is 48, the next unique .17407 starts at 49 and on it goes.

production.xlsb
	D	E	F
4			1197
5	UNIQUES	START POINT	RANGE
6	0.174	1	48
7	0.17407	49	50
8	0.18599	99	48
9	0.18893	147	72
10	0.19339	219	34
11	0.23872	253	34
12	0.2368	287	29
13	0.23424	316	60
14	0.23248	376	61
15	0.2244	437	54
16	0.2298	491	57
17	0.22648	548	58
18	0.22542	606	67
19	0.23245	673	67
20	0.23229	740	53
21	0.23202	793	60
22	0.23419	853	54
23	0.23846	907	47
24	0.23985	954	53
25	0.24251	1007	47
26	0.24264	1054	51
27	0.24275	1105	51
28	0.24198	1156	42
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUM(F6#)
D6:D28	D6	='CHAIN (5)'!Z12#
E6:F28	E6	='CHAIN (5)'!G12#
Dynamic array formulas.

My question is how can I get back from the "unique" column to the to original again? I've have had some success with creating a sequence and using the map function to map to it, also some partial success with transposing uniques with &sequence but nothing seems to pan, I always run into bycol or byrow mess I can't get out off.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=TOCOL(IF(SEQUENCE(,MAX(F6#))<=F6#,D6#,1/0),2)

 

[BEDE]

I've had some partial success with different combinations of the below

INDEX(D6#,SEQUENCE(SUM(F6#),,,1)/F6,) the ranges are obviously off but I can't get it to range by row.

TRANSPOSE(D6#)&SEQUENCE(F6#,ROWS(F6#),,0)

I can create to create a board then use map but I can't finish it.
SEQUENCE(MAX(F6#),COUNT(D6#),,0)
to get range.
TRANSPOSE(F6#)-BYCOL(SEQUENCE(MAX(F6#),COUNT(D6#),,0),LAMBDA(col,COUNT(col)))

 

[BEDE]

Hi & welcome to MrExcel.
How about

=TOCOL(IF(SEQUENCE(,MAX(F6#))<=F6#,D6#,1/0),2)

Woah testing now, thank you

 

[BEDE]

Question? so if less than max column range then unique else 1/0? I didn't know you could do that? What? 1/0?

 

[Fluff]

The 1/0 will simply return a #DIV/0! error which the tocol will then ignore.

 

[BEDE]

The 1/0 will simply return a #DIV/0! error which the tocol will then ignore.

Thank you very much for all your help. I'm not sure how to close thread or even edit but this is an amazing forum thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.