Creating Multiple sub-lists based on multiple criteria

Hello, I'm interning for a company this summer and running into a problem with a project I've been asked to take on.

In short, I need to reorder 13 separate sublists based on multiple criteria, in a way that groups similar properties and orders the sublist automatically. The main problem is that the size of these sublists changes every day, and so do some of the criteria.

I have some VBA experience but not enough to make this problem simple. Any tips would be really appreciated!

Thanks,

A

Can you post a workbook, showing an example (or two) of how the data comes in, and how you would like it ordered after? Detail the criteria you use to reorder the sublists.

Okay, first things first, to attach a workbook:
If you are replying using the "Quick Reply" click on the "Go Advanced" button.
There's a paperclick Icon that's supposed to work for attachments, but at least on my machine it's been non-working for a few months now. So, instead, go below and click on the "Manage Attachments" link. You'll get a window that will allow you to browse to and attach your workbook.
I'm leaving for the day, but will plan on trying to look at your attachment tomorrow morning (if time allows).

Thank you! I believe it attached.

It IS attached, but I don't see anything showing how the data is received, other than "it simply lists line, item, special, and status". Does that mean it comes to as a 4-column list, with headings as you've described, and values below the headings?

You mentioned a program that formulates scores already, so you're massaging the data received into some kind of format (maybe a 5 column table?). I think, if I understand correctly, you want to move from that format (which you didn't provide an example of) to the format you provided in your example. Is that correct? if so, we'll need to see what we're moving from.

Here's what I understand of your needs:
1) For each Line we need a subset
2) Within each subset (line) Status "Priority" needs to rise to the top, then need, then blank. However, in your example of line 1, the second line (order 2) is blank, and the bottom line is "Need", so I'm confused by your words and example.
3) If "Special" = 1, that's the highest Priority.
4) You describe scoring, which I don't understand, but you also mention you already have a program to do that, so I'm confused on this part.
5) You mention "These 3 things" (Priority, Special, Score?) "are what I need to influence the "order" column" (not sure what this means) "and populate number rankings individually for each separate "line" sublist." Do I assume these number rankings are outside the scope of what you're asking for?

To really understand this, I suggest an example of
1) The data as you receive it
2) A list of the steps you go through to put it in the order you want (steps in order, additional explanations to the side).
3) Example of how the data should look after going through the steps you follow.

The data as I receive it is in the same format for the most part, I have simplified the sheet for confidentiality purposes and as such cannot upload a direct file of how I receive the raw data because it lists company information. SO The 'Raw' tab shows how I receive the data and the 'Final' tab shows ideally how the finished schedule would look.

I need to create a number order for each line based on the hierarchy of production. If there are 8 Items scheduled to run on Line 1 I need to know in which order to run them (this is what I meant by number rankings, sorry for confusion). So the number order has to start over for each Line.


The hierarchy is as follows:
Status- 'Priority'
Status - 'Need'
Special - '1'
Lowest Score
Next Corresponding Score


*priorities must run before needs which run before specials which run before everything else (blanks in 'status' and 'special')

The second part of this is that we need to also take into consideration the 'score' assigned to each individual item. My logic behind this may be flawed, and maybe there is a better way to do this....
Basically, sorry I cannot elaborate more, each number listed in the 'score' column signifies a component/sub-assembly in each item. (6 = subassembly 1, 4 = subassembly 2, 3 = subassembly 3 and so on)
For example item 'a' contains the subassembly 6, so it is simpler, then we run item 'g' because it has sub-assemblies 6 and 4. This would mean that there are minimal changeover requirements. Which is the overarching goal, to reduce changeovers.

So the goal is to run items with the simplest score first, like 0 or 6, and build from there, progressing to add other numbers/sub-assemblies to the score. Whatever score follows must contain all the same numbers as the previous, and in some cases add another.
CAN: 6-6-64-654-6543
CANNOT: 6-61-64-651
IF there are no more similar scores the ordering must start over again with the next simplest available score.

Here are my steps when ordering by hand:
1) What is priority? List simplest 'score' priority first
2) repeat until all priorities are scheduled
3) What is need? List simplest 'need' priority first
4) repeat until all needs are scheduled
5) what is special? List simplest special first
6) repeat
7) look at remaining items, only blank status and special should be left, start with simplest score
8) build off of simplest score, stop when remaining scores are not compatible
9) insert changeover when restaring
10) restart with next simplest score, and build until no compatible left
11) repeat until schedule completed

I hope this makes my thought process more clear, and I really appreciate your help and patience!! Keep the questions coming if you have more

I think I understand, but I'm confused by "Changeover" in your "Final" tab. What is your criteria for putting in a "Changeover" line?

Another question (see picture):
For line one, Scores 643 appear first because Item AB has a status of Need, and so the rest of the 643s need to stick with it. Then, if I understand correctly, we go in order of simplest score, so 6, then I think it should be 62, but you have 64 first, then 62. What's the logic driving this?

it won't be hard to build a macro to do all of this for you if I can understand the logic. Line 9 also looks odd to me.

So the requirement for putting in a "changeover" line is whenever the "score" sequence/number can no longer be built upon by another item, and a new one must start. And physically in the factory production will have to stop to make appropriate changeovers for the new sequence

Example: 6-64-645 (no more items progress past 645) *changeover* 4-47-4237 *changeover* 5-45-45-45-4537

This is where I'm having the hardest time coming up with a logic system because 'priorities' take precedence over everything else, even if their 'score' is really high and could be built on before they are run. Because If you start with a 'Priority' item that has a score of 6547, you cannot switch to producing an item with a score of 6 without implementing a changeover first.

I hope that makes sense

If sequencing 62 to go before 64 makes more sense that is fine, it doesn't matter as much, the most important part is that because 62 and 64 have a disagreeing number they cannot be run one directly after the other without a changeover.

for line 9 'priorities' and 'needs' are run first, followed by 'specials' (AY 6). If you are referencing why (AZ 61) appears below the first changeover after (AY 6) it is because it does not have a 'status' or 'special' so it has to be prioritized lower, and thus must be run later

Tough nut to crack, still working. In your example file, on Raw, E40 the score value is blank. I see on your Final tab you just left that line off. is that the behavior to follow?

It is indeed and caused me many an hour of headaches. I noticed that as well and remedied the problem in my vlookup, for some reason the correct value didn't print, and the problem shouldn't arise in the future as all items should have a corresponding score. Just means I need to do another sweep through my data to make sure things are not missing or incomplete. I appreciate your help!

Amymaso,

I haven't given up, and I'm still confident of a solution, but I'm not there yet. I can only work on this during my free time at work, and I'm about to leave for the weekend, so won't get back to it until Monday. So, if someone else wants to take a crack at it over the weekend they're plenty welcome, otherwise I'll try to get back to it Monday.

Thanks Jomili! Let me know if you make any discoveries! If you can, I'd love to see the code that you have thus far, maybe what you have can get me jump started for now? Thanks

Need a clarification on "Built Upon". See the picture. In your final, you do a changeover from 643 (which rises to the top because of "need" to 6, because you can't "build upon" 643 to get to the next number. But if it's just a stepwise pattern, couldn't you "Step Down" to 64, then to 6, than back up to 62? or does that not work for your operation?

Sorry, forgot you wanted to see progress so far. See the attached. Go to the Raw(2) sheet. The data in A:F should be the same as in Raw, and the yellow table is my ranking logic. Run the macro "SortScore" and see if the result matches your "Final" ranking. I still need to work out a routine to fill in the "Changeover" parts, so I'll work on that unless you let me know of an issue that my code didn't fix. BTW, when all is said and done I'll delete the "New Status" column and convert the Order column to values. I've tried to comment the macro so you can see what each section is doing.

Forgot to mention, the result of the macro won't exactly match your "Final" tab, because you either made some mistakes on that tab or there's more to your logic than I'm understanding. For instance, on "Final", Line 3, Order 1 (Row 24) you have a score of 6 with "Need", but on "Raw" there's no 6 with need, but only 64 with Need. On line 4 in Final you have a 65 with Priority, but on raw only 651 has Priority. There may be other disconnects.

This is looking wonderful!!! I can't tell you how much I appreciate your help. This has filled so many holes in my code and taught me a lot.


To answer your question: unfortunately you cannot step down, as a number has been added to the "score" the manufacturing cannot back track once the new number is added, they have to start over from the beginning. So every time a 'score' no longer can be built upon the numbering has to start over to the next simplest, highest priority item.

Which brings me to my next question: I ran the code a few time and functionality is great but there is one things that needs to be changed. I've attached a picture below if what the ideal numbering should be and what the program is producing instead. You'll see what I mean when I say that the numbering starts with the highest as opposed to lowest, as well as the fact that this requires an extra changeover

I've noticed a few bugs on my own. Leaving for the day, but still working on it.

Thank you!

Oh my gosh! This is wonderful!! I'll need to run it through a few different scenarios, but it looks to be functioning very well! I'm so excited!
Thank you so much for your help and for leading me through how to do this. I'll keep you posted if anything comes up but I feel like I'll be able to handle anything if it does. THANK YOU!

Okay, I think this one has it. Only thing missing, and I'm not sure you'll need it, is a loop if you have multiple Needs in a Line. If that happens (or will happen) we'll need to put in a loop around "need" in the same way I did around "priority".
I have to admit, this was a tougher nut to crack than I expected it to be. As I said above, I'm sure one of the resident brainiacs could do it better, but this should get you started. Let me know if there's anything in the code you don't understand.