Sorting and analyzing data problems with multiple criteria

solarbear227

New Member
Joined
Oct 8, 2022
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. 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:

Overhaul SuggestedOverhaul ApprovedOverhaul doneRectification QuotationRectification works orderRegionEquipment no.Item AItem BItem CItem D
01.01.202205.09.2022Q1W1AE1
1​
1​
1​
02.01.202105.03.202106.07.2022Q2W2AE2
1​
1​
05.06.2020Q3W3AE3
1​
1​
1​
08.09.201620.10.201603.06.2017Q4W4BE4
1​
1​
04.08.2017Q5W5BE5
1​
1​
05.06.201504.10.201603.01.2017Q6W6CE6
1​
1​
06.02.2018Q7W7CE7
1​
1​
1​
08.08.2020Q8W8CE8
1​
1​
15.12.201311.12.201416.05.2015Q9W9DE9
1​
1​
1​
02.03.2022Q10W10DE10
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)

ItemPendingFinishedRequired
A125
B242
C111
D113

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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hello and welcome 😊
It's complicated to create formulas, that recognize the colors.
Instead I'll recommend making a "helper" column, e.g. put an "X" if an item is "finished" (you can the use conditional format, to keep your green colors).
Afterwards you can build your formulas, based on the helper column.

/Skovgaard
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top