Hi
I'm hoping for some help to create some stats on the status of working through client jobs. First I must state that I can't use a pivot table or Visual Basic solution as there is a shared workbook which I don't control. I've simplified client on boarding in an example. I was wondering if there is some way I could generate the stats without having to resort to building column of functions alongside the data.
The basic scenario is that periodically client's data must be reviewed. A client may have one office (known as an entity) or may have many. It makes sense to perform all the checks simultaneously with a list for a team to work through. For simplicity I've assumed for each entity a review moves between stages 1-5 before turning complete. If a client has only one entity and that is complete then that client is fully complete. If a client has multiple entities and some but not all are complete then the client may be said to be partially complete. If a client has nothing that has reached complete then they have the status Nothing Completed.
I had previously used a CSE function elsewhere and wondered could something like this be used and adjusted
Example 1
Example 2 - Sample data
Hoping someone can suggest how best to do this. Thanks in advance.
I'm hoping for some help to create some stats on the status of working through client jobs. First I must state that I can't use a pivot table or Visual Basic solution as there is a shared workbook which I don't control. I've simplified client on boarding in an example. I was wondering if there is some way I could generate the stats without having to resort to building column of functions alongside the data.
The basic scenario is that periodically client's data must be reviewed. A client may have one office (known as an entity) or may have many. It makes sense to perform all the checks simultaneously with a list for a team to work through. For simplicity I've assumed for each entity a review moves between stages 1-5 before turning complete. If a client has only one entity and that is complete then that client is fully complete. If a client has multiple entities and some but not all are complete then the client may be said to be partially complete. If a client has nothing that has reached complete then they have the status Nothing Completed.
I had previously used a CSE function elsewhere and wondered could something like this be used and adjusted
Example 1
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B10 | B10 | =COUNTIF('Feb 2020'!AN:AN,A10) |
| C10 | C10 | =SUM(--(FREQUENCY(IF('Feb 2020'!$AN$2:$AN$99999=A10,'Feb 2020'!$O$2:$O$99999),'Feb 2020'!$O$2:$O$99999)>0)) |
Example 2 - Sample data
| Fully Complete.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Data | Notes to Explain | |||||||
| 2 | Client | Sub Entity | Stage | Client | Status | ||||
| 3 | 1 | New York | Complete | 1 | Fully Complete | i.e. all Sub Entities are complete | |||
| 4 | 2 | London | 1 | 2 | Partially Complete | i.e. some Sub Entities are complete | |||
| 5 | 2 | Paris | Complete | 23 | Fully Complete | i.e. all Sub Entities are complete | |||
| 6 | 23 | Sydney | Complete | 463 | Partially Complete | i.e. some Sub Entities are complete | |||
| 7 | 23 | Dubai | Complete | 5555 | Nothing Completed | i.e. no Sub Entities are complete | |||
| 8 | 23 | New York | Complete | ||||||
| 9 | 463 | London | 1 | Results | |||||
| 10 | 463 | Paris | 2 | Clients | 5 | ||||
| 11 | 463 | Sydney | 3 | Fully Complete | 2 | i.e. Clients 1 and 23 | |||
| 12 | 463 | Tokyo | Complete | Partially Complete | 2 | i.e. Clients 2 and 463 | |||
| 13 | 5555 | Paris | 1 | Nothing Completed | 1 | i.e. Clients 5555 | |||
| 14 | 5555 | Singapore | 2 | ||||||
| 15 | 5555 | Tokyo | 3 | Complete | 4 | i.e. 4 Clients have a complete stage | |||
| 16 | 5555 | New York | 4 | ||||||
| 17 | 5555 | London | 5 | ||||||
Sheet1 | |||||||||
Hoping someone can suggest how best to do this. Thanks in advance.
