Formula to smooth/sort values over LinkID_SecrionID range

[longbow2000]

Good Day

I am looking for a formula to smooth and sort values for the LinkID_SecrionID range from biggest to lowest values, or lowest to biggest, depending on if max is in lower order of the range.

As per the inserted Mini Sheet, you have the following:

• LinkID and Section ID column
• Value assigned to each unique LinkID_SectionID
• Some LinkID_SectionID are repeated, but not always

The aim:

Based on the LinkID part, I aim to smooth the values, but in equal increments between the # of unique LinkID_SectionID. If the LinkID_SectionID is repeated, then the same value must apply.

My manual input is shown in the Mini Sheet, with my “side-line calculations”

Thanks in advance

Mr_Excel.xlsx
	A	B	C	D	E	F
1	LinkID_SectionID	Value	Desired_Outcome		Applicable to LinkID: D1088 (Range A2:B10)
2	D1088_010	353	678		Max	678
3	D1088_020	678	506		Min	163
4	D1088_030	546	335		Diff	515
5	D1088_030	546	335		Count Unique # LinkID_SectionID range	4
6	D1088_030	546	335		Increment	172
7	D1088_030	546	335		smooth/sorted values
8	D1088_030	546	335		Max	678
9	D1088_030	546	335		Smoothed value 1	506
10	D1088_040	163	163		Smoothed value 2 (will be repeated for D1088_030)	335
11	D1080_010	55	55		Min	163
12	D1080_010	55	55
13	D1080_020	42	42		Range(A11:B13) no change, only 2 unique values
14	D1083_010	128	128		Range(A14:B19) no change, only 2 unique values
15	D1083_010	128	128
16	D1083_010	128	128
17	D1083_010	128	128
18	D1083_010	128	128
19	D1083_020	59	59
20	D109_010	3644	4081		Applicable to LinkID: D109 (Range A20:B32)
21	D109_020	3285	3631		Max	4081
22	D109_030	4081	3182		Min	34
23	D109_040	3318	2732		Diff	4047
24	D109_050	1697	2282		Count Unique # LinkID_SectionID range	10
25	D109_060	1976	1833		Increment	450
26	D109_065	2062	1383		smooth/sorted values
27	D109_070	1414	933		Max	4081
28	D109_070	1414	933		Smoothed value 1	3631
29	D109_070	1414	933		Smoothed value 2	3182
30	D109_080	34	484		Smoothed value 3	2732
31	D109_080	34	484		Smoothed value 4	2282
32	D109_090	324	34		Smoothed value 5	1833
33	P28/5_010	6544	6544		Smoothed value 6	1383
34	P28/5_015	10397	13065		Smoothed value 7 (will be repeated D109_070)	933
35	P28/5_015	10397	13065		Smoothed value 8 (will be repeated for D109_080)	484
36	P28/5_015	10397	13065		Min	34
37	P28/5_015	10397	13065
38	P28/5_020	14427	19587
39	P28/5_020	14427	19587
40	P28/5_020	14427	19587		Applicable to LinkID: P28/5 (Range A33:B63)
41	P28/5_020	14427	19587		Max	32629
42	P28/5_020	14427	19587		Min	6544
43	P28/5_020	14427	19587		Diff	26085
44	P28/5_020	14427	19587		Count Unique # LinkID_SectionID range	5
45	P28/5_020	14427	19587		Increment	6521
46	P28/5_020	14427	19587		smooth/sorted values
47	P28/5_020	14427	19587		Min	6544
48	P28/5_025	12964	26108		Smoothed value 1	13065
49	P28/5_025	12964	26108		Smoothed value 2 (will be repeated for P28/5_015)	19587
50	P28/5_025	12964	26108		Smoothed value 3 (will be repeated for P28/5_020)	26108
51	P28/5_025	12964	26108		Max (will be repeated for P28/5_030)	32629
52	P28/5_030	32629	32629		Note: Max value in lower part of range, sort from min to max
53	P28/5_030	32629	32629
54	P28/5_030	32629	32629
55	P28/5_030	32629	32629
56	P28/5_030	32629	32629
57	P28/5_030	32629	32629
58	P28/5_030	32629	32629
59	P28/5_030	32629	32629
60	P28/5_030	32629	32629
61	P28/5_030	32629	32629
62	P28/5_030	32629	32629
63	P28/5_030	32629	32629
64	etc. to end as data set has a total of 210 different LinkIDs
Sheet2

Cell Formulas
Range	Formula
F2	F2	=MAX(B1:B10)
F3	F3	=MIN(B2:B10)
F4,F43,F23	F4	=F2-F3
F5	F5	=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
F6,F45,F25	F6	=F4/(F5-1)
C2:C4,C28,C43	C2	=F8
C5,C29:C30,C44	C5	=F10
C6,C31:C32,C45	C6	=F10
C7,C46	C7	=F10
C8,C47:C48	C8	=F10
C9:C10,C49	C9	=F10
F9:F11	F9	=F8-$F$6
F21	F21	=MAX(B20:B32)
F22	F22	=MIN(B20:B32)
F24	F24	=SUM(IF(A20:A32<>"",1/COUNTIF(A20:A32, A20:A32), 0))
F28:F36	F28	=F27-$F$25
F41	F41	=MAX(B33:B63)
F42	F42	=MIN(B33:B63)
F44	F44	=SUM(IF(A33:A63<>"",1/COUNTIF(A33:A63, A33:A63), 0))
F47	F47	=F42
F48:F51	F48	=F47+$F$45
C20:C27,C42	C20	=F27
C33:C34	C33	=F47
C35	C35	=F48
C36	C36	=F48
C37:C38	C37	=F48
C39	C39	=F49
C40	C40	=F49
C41	C41	=F49
C50	C50	=F50
C51:C52	C51	=F50
C53	C53	=F51
C54	C54	=F51
C55	C55	=F51
C56	C56	=F51
C57	C57	=F51
C58	C58	=F51
C59	C59	=F51
C60	C60	=F51
C61	C61	=F51
C62	C62	=F51
C63	C63	=F51

 

[longbow2000]

Good Day

As a follow-up to my previous query, I must admit with the latest Excel 365 builds (ver.2208) a lot of new formulas came into play, and I don’t know them all yet.

However, if it will be easier smoothing and ordering the values vua VBA, your input will also help ‘n great deal.

Thanks in advance