Help needed to create a function or vba code for this process

[VISHAL120]

Hi All,

I am really stuck with this problem since quite a long time and this is taking me a lot of time to do this manually.

i work with a planning system where i need to assign a group value and determine the single or group sequence. the purpose of this is used when loading the order number to determine if there will be changeover or not. therefore for every order which is group there are no changeover and the order number can be planned without any capacity limit.

the group number shall change everytime when the create value column for the group changes and a new group is assign together with the single or group sequence and also the pattern color is change for each group.

is there a function, vba code that we can create in order to place the group no, the single or group sequence and also instead of passing to subsidary column to facilitate the sumif for the group qty instead of doing this manually.

the problem when doing this manaully is not just time consuming but also lots of error has been occuring due to that.

thanks to see the attached file for better understanding.

many thanks in advance.

:banghead:

[Tinbendr]

is there a function, vba code that we can create in order to place the group no, the single or group sequence and also instead of passing to subsidary column to facilitate the sumif for the group qty instead of doing this manually.I'm still perplexed at what is changing.

Can you walk through what you are manually adding/changing? Please refer to the column headings to prevent any confusion.

[VISHAL120]

Hi ,

thanks first of all. i was just thinking if i have been able to clearly explain what am doing.

well here a bit what is being done :

I have also explained same on this attachment.


1. I need to concatenate the yarn and complexity together in order to determine a group type on column header CREATE VALUE FOR GROUP .

2. The group number is allocated manually ON THE COLUMN GROUP NO that is everytime yarn and the complexity remain same the group number shall remain same COMPARE TO PREVIOUS ROWS and if any one of these either the yarn or the Complexity changes ,the group also will change and a new group number is allocated based on the previous rows.

example as follow :

F:SH1_C
Here the Yarn has change but the Complexity is remaining the same so the group number also will change to a new group which is not in use.
F:SH2_C
F:SH3_C
here the yarn and complexity is remaining same when compare to the previous row that is why I allocate group 3 for the 4 orders which follows from the previous rows .The group number also shall not be a number that is already used. That is it shall be an increment number like if 1 is already use then it shall be 2,3,4,5,6
F:SH3_C
F:SH3_C
F:SH3_C
F:MXT_N


7
S
Like here the yarn is same but the complexity between the previous row is not same so I do gorup the previous one with the actual like the F: MXT_N WILL BE A NEW GROUP AND THE F:MXT_TWILL BE ANOTHER GROUP.
F:MXT_T


8
G
F:MXT_T


8
G

3. On the COLUMN GROUP OR SINGLE SEQUENCE here it will input the S or G for all orders which has a consecutive group number and for all that changes will be S

thanks also the see this attachment .

i hope i have been able to be a bit more precise than previously.

many thanks agai8n for the help.

[Tinbendr]

OK, I think I've got it now. But HOW are you adding records? Are they always added at the bottom, or could they be inserted in the middle?

I'm invisioning a userform that will handle the data input and selection, adding formulas as needed.

[VISHAL120]

Hi David,

thanks for the reply normally all records are added below and are sorted by complexity and yarn column and sometimes are added in between where i say the yarn or complexity are same.

thanks again for the help.

[mdmackillop]

I've added formulae into the two yellow cells, copied down, which checks out with your posted sample

[VISHAL120]

Hi ,

Many thanks for the help it works perfect after i have tried it till now, i just needed some advise more on that for the planning purpose.

Thanks to see the attached file as i have added the planning qty and also other data as it is actually and Moreover I am doing this manually for now. i just needed some advise of how can i formulate this or write a vba code for that as its too confusing somtimes and also risk of errors is high. i have been help before to create another planning system here itself but this one is completely different as it don’t uses the same formula.

The previous formula was as such ( taken from my another file that is why the row or column is not same as here just an idea:


=IF(OR($I45="",$K45="",$J45=""),0,IF(AH$43<$L45,0,IF(AH$43>=$L45,IF(0<($Z45),MIN(($Z45-SUM($AG45:AG45)),$AA45,SUMIF($AF$3:$AF$20,$AF45,AH$3:AH$20)-SUMIF($AF$44:$AF44,$AF45,AH$44)),0))))



Here is how i am doing actually:

1. When order is Single sequence I check the Min . Run and the Block date then i place the qty by day as per the min. run even though there may be capacity on the still to load ( See the formula on the attached file and example on order 10002 or row 10).

2. when there is No Min. Run I will put the full qty on 1 but if there is no cap then I split it into 2 or 3 days depending on the available capacity.( see order 10062 or row 16 for example).

3. If there is no Block Date i put the qty on the 1st available day ( see order 10012 or row 11 for example) that is Block dat and Min Run is optional here.

4. The same apply for order which has group Sequence but the only difference ( which is complex for me ) is I load the order with Min Run qty and Block date as per the first order within the same group number and for the whole group number the same min run is apply until the whole total qty has been loaded. Even though the Min run is changed it won’t accept ( see order with group 3 or row starting from 12 to 15 from example).

5. On the still to load I have place conditional formatting which indicates if am overloading or all loaded.


Well actually am doing this manually I just needed some advise of how can I formulate this or write a code in VBA .

[VISHAL120]

hI ,

Any help or advise please thanks in advance.

[VISHAL120]

Hi All

[VISHAL120]

Hi,

I have been able to place the formula which i mentioned previously but am still struggling for the order which group for the planning. thanks to see the attached file for the formula.

I will still struggle to place the formula for the group but i still need some more help and assistance from all of you.

thanking you in advance for your precious time.

please see the new attached file for the formula which is working actually but without taking into consideration the group or single sequence.
you change the min run and also the remove the block date to see it working to have an idea.


thanks a again.

[mdmackillop]

I don't understand from your posts exactly what you are trying to achieve and need assistance with. You need to be very specific, as we don't have the time to comprehend what complex sheets/formulae are intended to do.

[Capungo]

I tried to understand what you intend to do. But I couldn't get anything. Your explanations are not strong enough...

[VISHAL120]

Well I will really thank you both for the interest in my actual problem. I have been struggling since days and night on that since several weeks and till now am still and really need the help from you all. Many of my problems have been solved due to VBAX and am sure this one also will be solved with the help of you all.

Well I have tried to explain it more clearly and I think the below can help you understand it more and also please have a look at the new attachment with the amended formula.

I have been able to integrate the group sequence in the formula (But I still don’t feel and is still doubtful about the way its working) to my personal view even this formula is not working well for the all the order which has group Sequence.

For info order being plan in Single Sequence are colored green and for the Group Sequence its colored violet to distinguish

Well concerning the planning as you can see for the Single Sequence there is no problem its planning as normally as per the Min. Run and if ever there is no Min. Run the total qty is put on the 1st available cap.

I have inserted another column <Column L > With Header: Formula for the Min. Run for Group. Which holds the 1st Min. Qty declares on the start of the group until the last one within the same group number (see the formula on the column). And if Min Run for any orders within the same group number is change this becomes the new Min. Run as from the order which has changed and downwards. Then it plans as per the New Min Run again within the same group number for the remaining orders. See the attachment please.

The problem is, the way the qty is being plan for order which is group which is not correct. See orders as example starting from row 13 to 16 and row 19 to 21 ( this is not correct) as the Min Run shall be apply for all the order which are under the same group number and 2nd or 3rd order cannot start before the first order is not complete according to the Min Run declare.

Normally when a Min Run is declare at start of the group Sequence this shall be for all the orders having the same group number and no order shall start before the first one within the group is completed even though there is open capacity. See the attachment for examples.


I have done one example manually to show how it shall be for order being plan for group sequence. Example starts from row 23 to 30 for better understanding thanks to see the way it shall be working and then you can compare to the way it planning actually on row 13 to 16 which shall be not the case.

[VISHAL120]

Hi Good morning to all of you,

Any help on this or hint or advise please.

many thanks again .

[VISHAL120]

new attachement