solarbear227
New Member
- Joined
- Oct 8, 2022
- Messages
- 1
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hello everyone, this is my first time posting here. I hope I provide enough information about the problem I have.
Background information: The task given is to analyze data from an engineering project. The project is about the rectification work of aged equipment from different sites and regions and contains several main pieces of information to be analyzed. However, since the rectification work can proceed in two ways (by overhaul or rectification order), the data is kinda messed up in terms of integration.
The attached table below is a dummy to show what should be considered from a given set of data:
p.s. The Coloured work for rectification means it has been awarded (for quotation) or been done (works order)
The data I am looking for is whether the equipment still requires rectification by each item, where all the items are bound to each individual order.
I would like to create a graph showing if the rectification is pending, finished, or still requires attention to rectification.
As a pre-requisite, the subject engineering project's inspection is done after 01.08.2020, all overhauls done before shall not be considered as finishing the rectification.
The data is explained in the following logic:
1) If the works order is marked green (in the above table), the items of that subject shall be marked "finished".
2) If the quotation is awarded (marked red) and the works order has not been performed (not marked green), the items of the subject equipment shall be marked "pending"
3) If the overhaul is done after 01.08.2020, the items of that subject shall be marked "finished".
4) If the overhaul is approved but has not been performed, the items of that subject shall be marked "pending".
As a result, I would like to come up with a table as follows (not relating to the above table)
For the "required" can be obtained by simple sum and subtraction.
I have been using multiple conditional formatting for the project to find the "Pending" and "Finished" data, but it leads to a very long loading time for automatic calculation.
(with the coloring and sorting method, but my excel seldomly just crashes when performing the multiple formatting process)
I would like to know if there is any more straightforward method to find the required data, thanks!
Background information: The task given is to analyze data from an engineering project. The project is about the rectification work of aged equipment from different sites and regions and contains several main pieces of information to be analyzed. However, since the rectification work can proceed in two ways (by overhaul or rectification order), the data is kinda messed up in terms of integration.
The attached table below is a dummy to show what should be considered from a given set of data:
| Overhaul Suggested | Overhaul Approved | Overhaul done | Rectification Quotation | Rectification works order | Region | Equipment no. | Item A | Item B | Item C | Item D |
| 01.01.2022 | 05.09.2022 | Q1 | W1 | A | E1 | 1 | 1 | 1 | ||
| 02.01.2021 | 05.03.2021 | 06.07.2022 | Q2 | W2 | A | E2 | 1 | 1 | ||
| 05.06.2020 | Q3 | W3 | A | E3 | 1 | 1 | 1 | |||
| 08.09.2016 | 20.10.2016 | 03.06.2017 | Q4 | W4 | B | E4 | 1 | 1 | ||
| 04.08.2017 | Q5 | W5 | B | E5 | 1 | 1 | ||||
| 05.06.2015 | 04.10.2016 | 03.01.2017 | Q6 | W6 | C | E6 | 1 | 1 | ||
| 06.02.2018 | Q7 | W7 | C | E7 | 1 | 1 | 1 | |||
| 08.08.2020 | Q8 | W8 | C | E8 | 1 | 1 | ||||
| 15.12.2013 | 11.12.2014 | 16.05.2015 | Q9 | W9 | D | E9 | 1 | 1 | 1 | |
| 02.03.2022 | Q10 | W10 | D | E10 | 1 | 1 | 1 |
p.s. The Coloured work for rectification means it has been awarded (for quotation) or been done (works order)
The data I am looking for is whether the equipment still requires rectification by each item, where all the items are bound to each individual order.
I would like to create a graph showing if the rectification is pending, finished, or still requires attention to rectification.
As a pre-requisite, the subject engineering project's inspection is done after 01.08.2020, all overhauls done before shall not be considered as finishing the rectification.
The data is explained in the following logic:
1) If the works order is marked green (in the above table), the items of that subject shall be marked "finished".
2) If the quotation is awarded (marked red) and the works order has not been performed (not marked green), the items of the subject equipment shall be marked "pending"
3) If the overhaul is done after 01.08.2020, the items of that subject shall be marked "finished".
4) If the overhaul is approved but has not been performed, the items of that subject shall be marked "pending".
As a result, I would like to come up with a table as follows (not relating to the above table)
| Item | Pending | Finished | Required |
| A | 1 | 2 | 5 |
| B | 2 | 4 | 2 |
| C | 1 | 1 | 1 |
| D | 1 | 1 | 3 |
For the "required" can be obtained by simple sum and subtraction.
I have been using multiple conditional formatting for the project to find the "Pending" and "Finished" data, but it leads to a very long loading time for automatic calculation.
(with the coloring and sorting method, but my excel seldomly just crashes when performing the multiple formatting process)
I would like to know if there is any more straightforward method to find the required data, thanks!
