Help needed for layout display after calculation.

[VISHAL120]

Hi Everyone,

I need some help and advice to output a line balancing format for my supervisors on production lines. I have tried to explain the purpose as much as i can for understanding that is why the post is long.

I am actually using vlookup where possible and do the placement manually. Please refer to the attached file for better understanding of the way for the output results is being prepared for them on sheet [LINE LAYOUT TO PRINT].

Note : This is only a part of the file as this is the part we are actually facing issues.

We normally do all the analysis in office and after that we give them just the sheet [LINE LAYOUT TO PRINT] for them to better understand the machines placement on the production for each production lines and also the operation that are group and need to be done by operators / machines.

The data are taken from the sheet: [LAYOUT_ANALYSIS] and the data that need to place on the sheet [LINE LAYOUT TO PRINT] are as follows:

DATA FROM SHEET [LAYOUT_ANALYSIS]:


COLUMN A: which is the operation post sequence( right now I have decrease the size of it just to be able to post here otherwise this goes sometimes to 100 post depending on some of the orders)
COLUMN B: which are the operations description that need to be done.
COLUMN S: which is the mach.code ( to understand what machine to be used)
COLUMN U: which is the pieds ( presser foot to used by machines)
COLUMN R : which is the timing taken by operations.
COLUMN AE: which is the target that can be achieved by each operation post.
COLUMN AB: to know the operators allocated by each operation post.

These are the columns data that are used to create the line layout for the supervisor.

The problems we are facing are :


If you see the sheet [LINE LAYOUT TO PRINT] you will see the way it place the poste / machine according to the operation Sequencing and all the box we are actually doing manually so when there are 100 post we draw all these manually. Very time consuming sometimes just for that its almost 30 mins and not all box are of the same dimension sometimes see postes 7 where we have increase the line by 1 more as the operation description are more.
The problem I am facing with Vlookup is when we have group some operations on same post its not looking for the other operations description and we have to copy paste it manually. See poste number 4,6,7,14 and 15 on [LINE LAYOUT TO PRINT]for better understanding of what we are trying to do. See this also very time consuming for almost 2 hours
When operation poste like poste 7 which require 3 operators and poste15 requires 2 operators we have to duplicate the poste 7 ,3 times and place an identifier like 7-1,7-2,7-3 on sheet [LINE LAYOUT TO PRINT]. See sheet [LINE LAYOUT TO PRINT] on cell D29, R29, D37 where we place the identifier as such with different colors for better visibility. The operators requirement the column is COLUMN AB on sheet [LAYOUT_ANALYSIS] and if you refer to the cell AB21 you will see operator requirement is 3.


I have tried several ways to try to automate but have not been able to find a better solution till now.
I was thinking if we can do that with VBA actually but I have not yet figure it out for the moment and we are actually working on that as describe thus taking us enormous time and lots of errors due to copy paste just for displaying the information as all the analysis and calculation are already done in the office on the sheet [LAYOUT_ANALYSIS] which takes us around 30 mins for analysis only but almost 3 hours to show the display of the machine positioning..
Sometime we have like 100 lines with operations poste up to 60 to 120 to do daily.

I will appreciate your kind propositions and the possibility to do it with VBA as formula I guess we will still have issues especially when the operations details are group in 1 poste and we have to duplicate some postes based on the operators requirement and place the identifiers.

[SamT]

Regarding Post/Poste # 7:
Analysis, shows that [Coulisser doublure sur bavette devant+ret], [TPS MINS] = 1,22. Likewise, [OPERATOR REQUIRED] includes that value.
Print, Poste #7 (1,2,&3) shows [Coulisser doublure sur bavette devant+ret], [TPS MINS] = 0, and [TOTAL TEMPS:] does not include that value

Is that an error on this example, OR, is that correct.

Obviously, we need to know the Business Rules (https://duckduckgo.com/?q=Business+Rules&t=ffcm&ia=web) that your team uses when manually creating this Report.
The most efficient method to create a complete set of BR is to have one or more competent persons write them from memory, then have all team member use and correct them while working.

It is impossible to create good systems without knowing all BR.

WOW, That analysis sheet has about 100 complex formulas per row! I bet that takes a while to fully calculate. You might want to consider Refactoring that system to use VBA and Arrays. IMO, a well developed system should not take one computer more than ten minutes (per 1000? rows,) from importing the Raw Data to delivering the Print sheet.

[VISHAL120]

Hi SamT,

Thank you for your kind intervention and advise in advance. I sincerely appreciate your time and consideration.

for this point as you mentioned quoted [["Regarding Post/Poste # 7:
Analysis, shows that [Coulisser doublure sur bavette devant+ret], [TPS MINS] = 1,22. Likewise, [OPERATOR REQUIRED] includes that value.
Print, Poste #7 (1,2,&3) shows [Coulisser doublure sur bavette devant+ret], [TPS MINS] = 0, and [TOTAL TEMPS:] does not include that value

Is that an error on this example, OR, is that correct.]]

Yes you found it right its in fact an error and this is the type of mistakes that my team are doing when copy and pasting on the sheet [LINE LAYOUT TO PRINT] .In fact the timing for this operation is 1.22, i have attached a new file where i have corrected this error. thank you for notifying me .

quoted" Obviously, we need to know the Business Rules (https://duckduckgo.com/?q=Business+Rules&t=ffcm&ia=web) that your team uses when manually creating this Report."

SamT: the Business rule for it is as follows:

1. as i have explained we are just seeing the number of poste operation that we have from the sheet LAYOUT_ANALYSIS from column A which is actually 35 if you go to the row 69 you will see. ( normally this is not fixed for every order and production lines this is the one which varies sometimes to 20 to even 100 depending on the number of poste operations we have .)

2. Then from this last poste operation number which here is actually 35 we will start copy pasting the rectangular boxed that you see on the sheet LINE LAYOUT TO PRINT actually till we have 35 rectangular boxed like that.

3. Since i have already placed the vlookup formula in the rectangular box, this helps us when the postes operations has single operation description like example operation poste 1,2,3,5,8, etc.

4. For the one which are group we do copy paste like poste operation 4,6,7,14,15 as values from sheet LAYOUT_ANALYSIS to sheet LINE LAYOUT TO PRINT on the concern rectangular box sequence number as you see on the sheet LINE LAYOUT TO PRINT for these poste operations 4,6,7,14,15

5. For the operation poste where it requires more than 1 operator like the operation poste 7 as example , we will check the number of operators mentioned in sheet LAYOUT_ANALYSIS in column AB. LIke here its 3 for the operation postes 7 on the row 21. We will then duplicate the rectangular box number 7, 3 times and then add the identifier like 7-1 ,7-2,7-3 and set a different color for indentification and for them to understand that this operation poste require 3 operators for all the operations listed in poste 7.

6. Whenever the operation description is more for a concern rectangular boxed like again as example operation poste 7 we will add more lines in between to make it fit in between the rectangular box.

These are the steps that we are following to make the data clearer for our line supervisors in the sheet LINE LAYOUT TO PRINT.

I hope you have got the idea on how we are proceeding for the BR.

quoted :"WOW, That analysis sheet has about 100 complex formulas per row! I bet that takes a while to fully calculate. You might want to consider Refactoring that system to use VBA and Arrays.

Yes in fact it has a lot of formula in it but it don't take that much of time to calculate. i have set the calculation to manual and set a VBA code, so when we click calculate then the calculation are done.

As i said before its just part of the sheets that i have send as these are the 2 sheets that we just need to find the best solution to do it, without that much of copy pasting and complete the task on about 10 mins rather than now its taking us more than 1-2 hours to complete only one sometimes just to build the line layout.

quoted:" You might want to consider Refactoring that system to use VBA and Arrays."

I have build this in an urgency as we are actually changing some system in our company and i have not really got the time to really do it in VBA actually for sure going forward i will have to see the best alternative with VBA to make the file lighter as its already actually 25MB now. I think by using VBA afterwards we can decrease the file weight to nearly 3-5MB and also increase the speed of calculation.

But for now this part that i am looking for is a real headache the making of the LINE LAYOUT TO PRINT :

its really very very time consuming and high risk of error as you have seen already.

Frankly to be honest this error was not done purposely, it actually what we really faced in real and its the production supervisors who will come every time to make correction in the office.

I sincerely thank anyone in advance who can help me out on it with any proposals with VBA.

[SamT]

Ok. Dealing only with generating some Print Blocks, These are the BR that I came up with. Please verify and correct.

Some Definitions from the Analysis sheet:


PosteNumber: Value in Column A
PosteRangeBlock: A Group of Rows with the same PosteNumber
PosteOperations: All Values from Column B of all Rows in PosteRangeBlock
PosteTPSMins: All Values from Column R of all Rows in PosteRangeBlock
PosteMachCode: All Values from Column S of all Rows in PosteRangeBlock
[*=1]Verify. This is not true for Poste 7 on Print sheet
PostePieds:All Values from Column U of all Rows in PosteRangeBlock
[*=1]Or
[*=1]PostePieds:Value of Column U in the top Row of each PosteRangeBlock
PosteTrgt: Value of Column T in the top Row of each PosteRangeBlock
LayOutBlocks Required: Value of Column AB in the top Row of each PosteRangeBlock
[*=1]See Print Sheet Definition




Some Definitions and Rules of PostePrintBlock from Print Sheet
MinimumHeight: 6 Rows
Width: 11 Columns
Cells(1,1) = PosteNumber
Cells(1,2) = From LayoutBlocksRequired, if more than 1. Increment Cell by Decimal integers. Example (PosteNumber).1 to (PosteNumber).As needed

Other than Cells(1,2) all other items on all PostePrintBlocks with the same Poste number are identical

Cells(1, 5) to Cells(1,10) = Standard PostePrintBlock header
Cells(2,1 to Cells(As Required,2) = PosteOperations
Cells(2,6) to Cells(As Required,6) = PosteMachCode .Verify: See Poste 7
Cells(2,7) to Cells(As Required,7) = PostePieds
Cells(2,8) to Cells(As Required,8) = PosteTPSMins
Cells(2,9) = PosteTrgt
Cells(LAstRow,7) = "TotalTemps"
Cells(LastRow,8) = Sum(PosteTPSMins)

[SamT]

Some other PostePrintBlock BR I just thought of
Referring to your attachement,
suppose Poste# 2 had 10 lines. How would you Vertically Align Poste#3 Block?
Suppose Poste#2 and # 7 each had 10 lines. What would the Alignments look like.

This question requires Simple Business Rules like

Blocks shall be placed in alternating columns in created sequence
The Tops of Blocks in the Right Column shall align with the tops of Blocks in the left column
Blocks will alternate between left and Right columns, regardless of Poste#
Each block will have at least two empty rows below it


Or Hard to Follow rules like


Each Block will be below the preceding block, except Rule 2
The Columns of Blocks will be approximately equal in height

[VISHAL120]

Hi SamT,

here below the corrections where needed and validation:

Some Definitions from the Analysis sheet:



PosteNumber: Value in Column A : CORRECT
PosteRangeBlock: A Group of Rows with the same PosteNumber : CORRECT
PosteOperations: All Values from Column B of all Rows in PosteRangeBlock : CORRECT
PosteTPSMins: All Values from Column R of all Rows in PosteRangeBlock : CORRECT
PosteMachCode: All Values from Column S of all Rows in PosteRangeBlock : CORRECT
Verify. This is not true for Poste 7 on Print sheet: Yes it will not be the same as different poste operations can have different machine code.
PostePieds:All Values from Column U of all Rows in PosteRangeBlock : CORRECT
Or
PostePieds:Value of Column U in the top Row of each PosteRangeBlock :No each poste operations can have their own pieds to use. It same as the timing where each poste operations descriptions has their own timing allocated.
PosteTrgt: Value of Column T in the top Row of each PosteRangeBlock:No its the value of column AE that should be on top of each PosteRangeBlock.
LayOutBlocks Required: Value of Column AB in the top Row of each PosteRangeBlock:Not necessary to display it on the rectangle boxed. But it is the column that we refer to know how many operators are allocated for a specific postenumber in order to know how many rectangular boxed need to be duplicated.
See Print Sheet Definition





Some Definitions and Rules of PostePrintBlock from Print Sheet
MinimumHeight: 6 Rows : YES OK
Width: 11 Columns : YES OK
Cells(1,1) = PosteNumber : YES OK
Cells(1,2) = From LayoutBlocksRequired, if more than 1. Increment Cell by Decimal integers. Example (PosteNumber).1 to (PosteNumber).As needed
Other than Cells(1,2) all other items on all PostePrintBlocks with the same Poste number are identical : YES OK
Cells(1, 5) to Cells(1,10) = Standard PostePrintBlock header : YES OK
Cells(2,1 to Cells(As Required,2) = PosteOperations : YES OK
Cells(2,6) to Cells(As Required,6) = PosteMachCode .Verify: See Poste 7 : YES OK IT WILL BE DIFFERENT FOR EACH OPERATION DESCRIPTION
Cells(2,7) to Cells(As Required,7) = PostePieds : YES OK
Cells(2,8) to Cells(As Required,8) = PosteTPSMins : YES OK BUT IT WILL BE DIFFERENT FOR EACH OPERATION DESCRIPTION
Cells(2,9) = PosteTrgt : YES OK
Cells(LAstRow,7) = "TotalTemps" : YES OK
Cells(LastRow,8) = Sum(PosteTPSMins) : YES OK

QUOTED:"
Some other PostePrintBlock BR I just thought of
Referring to your attachement,
suppose Poste# 2 had 10 lines. How would you Vertically Align Poste#3 Block? better we keep 1 row blank in between another rectangular box poste.
Suppose Poste#2 and # 7 each had 10 lines. What would the Alignments look like. this does not matter even the rectangular boxed are not align as it is actually the sheet LINE LAYOUT TO PRINT till there will be the poste number we can follow the sequence.

This question requires Simple Business Rules like


Blocks shall be placed in alternating columns in created sequence. Yes it shall follow the operation post number sequence so as to give it a continuity flow.
The Tops of Blocks in the Right Column shall align with the tops of Blocks in the left column. No not necessary.
Blocks will alternate between left and Right columns, regardless of Poste# Yes it does not matter if any alternation is happening till the follow the sequence of operation poste number.
Each block will have at least two empty rows below it. YES OK



Or Hard to Follow rules like



Each Block will be below the preceding block, except Rule 2.Yes it shall be below each preceding block and in between we shall have a blank row or line just to separate each block.
The Columns of Blocks will be approximately equal in height. Yes we can keep the same height for each column no problem.

[SamT]

I don't see a simple way to do this.

What I am thinking about at the moment (Part time) is one standard Procedure to start the works, One Class to read the data and break it into Pointes , one class to read each Pointe, and one class to write each Pointe "Block" to the Layout sheet.

[VISHAL120]

Hi SamT,

Thank you for the reply.

I will be grateful for that please if we get the idea of doing it.

thank you in advance Sir.