Office hacks: Excel to assist production processes do not conflict

Because Excel comes with schedules and functions, so we often produce some and date related flow chart in Excel. But just simply write the date, so that it is not a good way to avoid date conflicts in the process, in fact, with the use of Excel functions and conditional formatting and other tools, we can create a more scientific flow chart table. For example, a company has three machining centers, respectively, can be processed in different departments of the parts, the production department to develop a production flow sheet, you need to check each process in the selection of different machining centers, processing tasks will not conflict, if a conflict occurs, you need to prompt the cause of the conflict, and re-select the processing center or production time to avoid conflict (Figure 1).


Fig. 1 Conflict flow chart can be checked

From the above case, we can see that different products have a production time after selecting each machining center, and if the same machining center overlaps the time when machining different products, this will lead to conflicts. For checking the conflict, we can check it with the help of comparing the end time and start time of the tasks before and after. In the above case, Task 1 and Task 2 both use “Machining Center 1”, and the end time of Task 1 is 2019/11/7, but the start time of Task 2 is 2019/11/6, so there is a conflict between the two tasks. To achieve the above effect, we only need to solve the following three problems.

1. the task number corresponding to each machining center

2. The start and end time of each task corresponding to the machining center

3. Add text alerts and color reminders according to the conflict or not

First is to extract the task number, task number extraction can be done with the help of LOOKUP function, positioned in cell H2, enter the function “= IF (F2 = “”, NA (), LOOKUP (D2, IF ($ F $ 1: F1 = F2, $ D $ 1: D1, NA ()), $ A $ 1: A1)) “, which is an IF nested array function that uses the LOOKUP function for task number extraction, where the task number is extracted from the machining center number selected by column F. After completing the input of the function press Crrl+Shift+Enter to complete the input of the array function, so that the drop-down can be extracted to meet the requirements (time conflict) of the task number (Figure 2).


Figure 2 extract the task number

Next, according to the task number to extract the beginning and end of the task time, locate the cell J2 enter the function “=VLOOKUP(H2,$A$2:$E$6,4,0)”, the meaning of this function is in the A2:E6 interval to find the start time value, the value in the search area of the fourth column (column D), find the start of the task of filling the time to the corresponding cell in column H. This drop-down formula if there is a conflict of task number, the start time of the task will be automatically extracted (Figure 3).


Figure 3 extract the start time

Continue to enter the formula “=VLOOKUP(H2,$A$2:$E$6,5,0)” in cell K2 to extract the end time of the corresponding task. Positioned to I2 cell enter the formula “=IF (K2>=D2, TRUE,” “), where the IF function is used to determine the comparative value of time, if K2 cell (that is, the end time of the previous task) is greater than D2 cell (that is, to compare the start time of the task), then it is displayed as TRUE, indicating that there is a time conflict. For example, the end time of task 1 is 2019/11/7 is greater than the start time of task 2 2019/11/6, so there is a conflict for that task. Note here that the comparison is between tasks of the same machining center, and the conflicting cell I3 is displayed as TRUE (Figure 4).


Figure 4 Compare times

For tasks displayed as TRUE, you can use the “&” (hyphen) to customize the displayed conflict reminder character. In the example the reminder character is “Conflict! Task with processing – Task 1: Workshop 1, Turbo”, you can see the format of the reminder text: ” Conflict! processing task -” + “H column of the extracted task number” + “task number corresponds to the contents of columns B and C”, so the reminder character can be used to fix the character + H + B + C column format combination. Positioned to cell L2 enter the formula “=” conflict! There are processing tasks – “&H2&”: “&VLOOKUP(H2,$A$2:$E$6,2,0)&”,”&VLOOKUP(H2,$A$2:$E$6,3,0)”, here the contents of columns B and C are extracted using the VLOOKUP function, while using the “& ” character to connect (Figure 5).


Figure 5 character connection

positioned to the G2 cell enter the formula “= IF (I2 = TRUE, L2,” “)”, that when the I2 cell is displayed as TRUE (indicating that there is a conflict in the time of the task), then G2 cell will display the contents of L2 cell (that is, the text of the above settings), so that the task for the conflict in the G column can be normal display of the above reminder text, it is convenient for us to quickly view which task and the current task conflict, and the actual processing of conflicting tasks can also be seen at a glance (Figure 6).


Figure 6 View conflicting tasks

Of course, if there are many conflicting tasks, we can also use the conditional formatting for eye-catching reminders, select the “A2:D6” region, click “Start → Conditional Format → Text Inclusion”, select the cell contains the text ” conflict!” , the cell color is filled with “yellow” (Figure 7).


Figure 7 Conditional formatting settings

So that when we check out the conflicting tasks in the flow sheet, the reminder cell will appear the above reminder text, and automatically activate the conditional format, the cells of these tasks will be automatically filled with yellow, at this time can be adjusted by selecting a different processing center (you can add a processing center drop-down list in column F to select) or adjust the start time, in order to facilitate viewing can also be other unrelated Copy all columns hidden (Figure 8).


Figure 8 Showing conflicting tasks

Of course, here we just use the production process as an example, you can apply it to multiple meeting room arrangements, a presenter in multiple meetings of the hosting tasks, etc.


Leave a Comment