Forum Discussion
What Assets have been service but not on the schedule?
Hi, I hope you can help please.
i have two excel spread sheets, DATA1 and DATA2.
DATA1 shows a service schedule for a set of ASSETS which are being serviced for three different SITES.
DATA2 shows the results of the services of the ASSETS over the different SITES.
Issue to show is, ASSETS may be serviced (DATA2), however it is not listed in the service schedule DATA1. Example, ASSET BB was serviced (DATA2) at SITE 22 but was not listed to be serviced in the service schedule DATA1.
How do I highlight this in DATA1 to show which ASSETS are serviced but not listed in the service schedule DATA1? Highlighted in a Pivot Table.
A Pivot Table is created so I can present what ASSETS are being serviced but are not in the service schedule.
DATA2 - Service Results
| SITE | ASSET | ASSET NUMBER | STATUS |
| 11 | AA | 2500 | Active |
| 11 | BB | 2599 | Active |
| 11 | CC | 4903 | Decommission |
| 22 | AA | 2802 | Active |
| 22 | BB | 1009 | Active |
| 22 | CC | 8999 | Active |
| 33 | AA | 2763 | Active |
| 33 | BB | 3890 | Active |
| 33 | CC | 4362 | Active |
DATA1 - Service Schedule
| ASST | SITE | SCHEDULE DATE |
| AA | 11 | 1/04/2023 |
| BB | 11 | 1/04/2023 |
| CC | 11 | 1/04/2023 |
| AA | 22 | 1/06/2023 |
| BB | 22 | |
| CC | 22 | 1/06/2023 |
| AA | 33 | 1/08/2023 |
| BB | 33 | 1/08/2023 |
| CC | 33 | 1/08/2023 |
Here is an example file as far as I understand it.
Method:
First open a new column in DATA1 and enter this formula:
=IF(AND(COUNTIFS(DATA2!$B:$B, $A2, DATA2!$A:$A, "<>"&$B2)>0, $C2=""), "Serviced, Not Scheduled", "")
Then from D2 selected all cells below and opened a new rule in conditional formatting and enter this formula:
=D2<>""
In the sample file you can see how it is structured.
I hope that I understood the task correctly and that it helps you.
