Sorting: Prioritizing Group retention over Order

[stretch2011]

Hello,

Long time reader, first time post. I'm working on a project at work to try and automate some portions of schedule creation for our assets. I work in manufacturing. I'm pretty descent with excel, or at least pretty descent at googling what I want to do. I'm good up until it gets into VBA coding and I think the solution I'm looking for requires it ( I may be wrong).

If any guru's out there want to take a stab at this, or point me in the right direction, I would greatly appreciate it. Requirements are in the mini sheet.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Before							After
2	Location	Part #	Due Date	Asset #	Insert Type	Color Type		Location	Part #	Due Date	Asset #	Insert Type	Color Type
3	1	1		1	$I	$N		1	1	10/08/2024	1	$I	$Y
4	1	1		1	$I	$O		1	1	10/09/2024	1	$F	$V		1.) Ordering by number A-Z is not important, Minimilizing change is important.
5	1	1		1	$F	$O		1	1		1	$F	$M
6	1	1		1	$F	$Y		1	1		1	$I	$M		2.) Grouping Hierarchy
7	1	1	10/09/2024	1	$F	$V		1	1		1	$F	$N		1st - Location : Locations don’t mix so what happens inside of 1 location doesn’t affect another
8	1	1		1	$F	$Q		1	1		1	$I	$N		2nd- Earliest Due date: Avoid past dues. Some flexibility in this.
9	1	1		1	$F	$N		1	1		1	$I	$N		3rd- Asset # : Changing assets is very tedious, so keeping assets grouped is high priority.
10	1	1		1	$F	$M		1	1		1	$F	$O		3.a- Assets do not need to be A-Z, just grouped together ( ex. all 1's are together)
11	1	1		1	$I	$M		1	1		1	$I	$O		4rd- Color type: Easier to change a color than an asset, but harder than changing insert type
12	1	1		1	$I	$N		1	1		1	$F	$Q		5th- Insert changes
13	1	1		1	$I	$X		1	1		1	$I	$Q
14	1	1	10/08/2024	1	$I	$Y		1	1		1	$I	$U		3.) Insert type and color type columns were derived from full part numbers.
15	1	1		1	$I	$U		1	1		1	$I	$X
16	1	1		1	$I	$Q		1	1		1	$F	$Y		4.) The standard multilevel sort does not work. Gets around 80% of the way there.
17	2	2	10/03/2024	2	$I	$N		2	3	09/25/2024	3	$F	$N		It will sort the locations, and assets together, but it does not dynamically change
18	2	3	09/25/2024	3	$F	$N		2	3		3	$F	$O		the sort order based off of the cell above.
19	2	3		3	$F	$O		2	5		5	$F	$O		Set sort order ex
20	2	4		4	$F	$O		2	5	09/25/2024	5	$F	$N		1st (N)
21	2	4		4	$F	$V		2	2	10/03/2024	2	$I	$N		2nd (O)
22	2	4		4	$F	$U		2	13	09/26/2024	13	$F	$V		3rd (P)
23	2	4		4	$F	$Q		2	13	10/01/2024	13	$F	$O		4th €
24	2	4		4	$I	$Q		2	13	10/01/2024	13	$I	$O		If a asset (1) ends on color $O and asset (2) has colors $N,$O, and $P
25	2	4		4	$I	$N		2	13	10/04/2024	13	$I	$U		it will order asset (2) as $N-$O-$P per the standard sort order format.
26	2	4		4	$I	$O		2	13	10/07/2024	13	$I	$M		The prefered result would be to sort asset (2) as $O-$P-$N since the last asset
27	2	4		4	$I	$Y		2	13	10/18/2024	13	$F	$M		ended on $O
28	2	5	09/25/2024	5	$F	$N		2	13	10/07/2024	13	$I	$N
29	2	5		5	$F	$O		2	7	10/09/2024	7	$F	$N		5.) Another way of looking at it would be a running sort order that references the above cell to determine the sequence
30	2	6		6	$I	$U		2	7		7	$F			inside the groups below.
31	2	7	10/09/2024	7	$F	$N		2	6		6	$I	$U
32	2	7		7	$F			2	8		8	$F	$N		6.) Typical row count is 250-350 line items in normal spreadsheet.
33	2	8		8	$F	$N		2	8		8	$F	$O
34	2	8		8	$F	$O		2	9		9	$F	$O
35	2	9		9	$F	$O		2	10		10	$F	$O
36	2	10		10	$F	$N		2	10		10	$F	$U
37	2	10		10	$F	$U		2	10		10	$F	$N
38	2	10		10	$F	$O		2	11		11	$F	$N
39	2	11		11	$F	$N		2	4		4	$I	$N
40	2	12		12	$F	$Q		2	4		4	$F	$O
41	2	13		13	$F	$N		2	4		4	$I	$O
42	2	13	10/01/2024	13	$I	$O		2	4		4	$F	$U
43	2	13	10/01/2024	13	$F	$O		2	4		4	$F	$V
44	2	13	09/26/2024	13	$F	$V		2	4		4	$I	$Y
45	2	13	10/18/2024	13	$F	$M		2	4		4	$I	$Q
46	2	13		13	$F	$N		2	4		4	$F	$Q
47	2	13	10/04/2024	13	$I	$U		2	12		12	$F	$Q
48	2	13		13	$I	$Q		2	13		13	$I	$Q
49	2	13	10/07/2024	13	$I	$M		2	13		13	$F	$N
50	2	13	10/07/2024	13	$I	$N		2	13		13	$F	$N
51	2	13		13	$I	$Y		2	13		13	$F	$U
52	2	13		13	$F	$U		2	13		13	$I	$Y
After