PaulyK
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
HI,
I have been using the =UNIQUE with the =SORT function to good effect. However I have a list of Names and Projects which contain a Number at the end (either with or without a space before) and am unable to sort them in order - i.e Project 1, Project 2, Project 3
New to using these functions but is there a way to achieve the desired result?
Thanks
Pauly
LISTS
Data Source
I have been using the =UNIQUE with the =SORT function to good effect. However I have a list of Names and Projects which contain a Number at the end (either with or without a space before) and am unable to sort them in order - i.e Project 1, Project 2, Project 3
New to using these functions but is there a way to achieve the desired result?
Thanks
Pauly
LISTS
Resource Allocation Tool - RAT - Example.xlsx | |||||
---|---|---|---|---|---|
F | G | H | |||
1 | Project | Pipeline Project | People | ||
2 | Project 1 | New Project2 | Person 1 | ||
3 | Project 2 | New Project3 | Person 2 | ||
4 | Project 3 | New Project4 | Person 3 | ||
5 | Project 8 | New Project5 | Person 20 | ||
6 | Project 9 | New Project6 | Person 13 | ||
7 | Project 10 | NewDemandProject1 | Person 14 | ||
8 | Project 11 | NewDemandProject2 | Person 8 | ||
9 | Project 13 | NewDemandProject3 | Person 6 | ||
10 | Project 14 | NewDemandProject4 | Person 15 | ||
11 | Project 15 | NewProject1 | Person 10 | ||
12 | Project 16 | Person 26 | |||
13 | Project 17 | Person 4 | |||
14 | Project 18 | Person 7 | |||
15 | Project 19 | Person 11 | |||
16 | Project 20 | Person 9 | |||
17 | Project 21 | Person 23 | |||
18 | Project 22 | Person 18 | |||
19 | Project 23 | Person 5 | |||
20 | Project 24 | Person 27 | |||
21 | Project 25 | Person 25 | |||
22 | Person 12 | ||||
23 | Person 17 | ||||
24 | Person 16 | ||||
Lists |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F21 | F2 | =UNIQUE(TblProjAlloc[PROJECT NAME]) |
G2:G11 | G2 | =SORT(UNIQUE(Table4[PROJECT NAME])) |
H2:H24 | H2 | =UNIQUE(TblProjAlloc[WHO]) |
Dynamic array formulas. |
Data Source
Resource Allocation Tool - RAT - Example.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | PROJECT NUMBER | PROJECT NAME | WHO | ||
2 | 190105 | Project 1 | Person 1 | ||
3 | 190105 | Project 1 | Person 2 | ||
4 | 190165 | Project 2 | Person 3 | ||
5 | 190165 | Project 2 | Person 20 | ||
6 | 190165 | Project 2 | Person 13 | ||
7 | 190165 | Project 2 | Person 14 | ||
8 | 200066 | Project 3 | Person 8 | ||
9 | 200067 | Project 8 | Person 6 | ||
10 | 200067 | Project 8 | Person 15 | ||
11 | 200067 | Project 8 | Person 3 | ||
12 | 200067 | Project 8 | Person 10 | ||
13 | 200067 | Project 8 | Person 26 | ||
14 | 200067 | Project 8 | Person 1 | ||
15 | 200078 | Project 9 | Person 4 | ||
16 | 200078 | Project 9 | Person 10 | ||
17 | 200087 | Project 10 | Person 20 | ||
18 | 210001 | Project 11 | Person 1 | ||
19 | 210001 | Project 11 | Person 15 | ||
20 | 210004 | Project 13 | Person 7 | ||
21 | 210004 | Project 13 | Person 11 | ||
22 | 210004 | Project 13 | Person 9 | ||
23 | 210004 | Project 13 | Person 23 | ||
24 | 210007 | Project 14 | Person 1 | ||
25 | 210007 | Project 14 | Person 18 | ||
26 | 210007 | Project 14 | Person 26 | ||
27 | 210007 | Project 14 | Person 5 | ||
28 | 210007 | Project 14 | Person 27 | ||
29 | 210007 | Project 14 | Person 9 | ||
30 | 210007 | Project 14 | Person 25 | ||
31 | 210007 | Project 14 | Person 4 | ||
32 | 210007 | Project 14 | Person 8 | ||
33 | 210007 | Project 14 | Person 23 | ||
34 | 210007 | Project 14 | Person 12 | ||
35 | 210008 | Project 15 | Person 3 | ||
36 | 210008 | Project 15 | Person 17 | ||
37 | 210008 | Project 15 | Person 6 | ||
38 | 210008 | Project 15 | Person 12 | ||
39 | 210010 | Project 16 | Person 2 | ||
40 | 210015 | Project 17 | Person 18 | ||
41 | 210022 | Project 18 | Person 15 | ||
42 | 210024 | Project 19 | Person 15 | ||
43 | 210028 | Project 20 | Person 6 | ||
44 | 210028 | Project 20 | Person 15 | ||
45 | 210036 | Project 21 | Person 18 | ||
46 | 210037 | Project 22 | Person 16 | ||
47 | 210044 | Project 23 | Person 3 | ||
48 | 210044 | Project 23 | Person 17 | ||
49 | 210055 | Project 24 | Person 18 | ||
50 | 210062 | Project 25 | Person 13 | ||
51 | 210062 | Project 25 | Person 23 | ||
52 | 210062 | Project 25 | Person 10 | ||
53 | 210062 | Project 25 | Person 27 | ||
54 | |||||
55 | |||||
56 | |||||
57 | Demand / Pipeline Projects (Not yet approved) | ||||
58 | Stage | PROJECT NAME | WHO | ||
59 | Demand | NewDemandProject1 | Person 1 | ||
60 | Demand | NewDemandProject1 | Person 2 | ||
61 | Demand | NewDemandProject1 | Person 4 | ||
62 | Demand | NewDemandProject2 | Person 1 | ||
63 | Demand | NewDemandProject2 | Person 2 | ||
64 | Demand | NewDemandProject2 | Person 3 | ||
65 | Demand | NewDemandProject3 | Person 2 | ||
66 | Demand | NewDemandProject3 | Person 4 | ||
67 | Demand | NewDemandProject3 | Person 6 | ||
68 | Demand | NewDemandProject4 | Person 1 | ||
69 | Demand | NewDemandProject4 | Person 10 | ||
70 | Demand | NewDemandProject4 | Person 11 | ||
71 | PipeLine | NewProject1 | Person 1 | ||
72 | PipeLine | NewProject1 | Person 4 | ||
73 | PipeLine | New Project2 | Person 2 | ||
74 | PipeLine | New Project3 | Person 7 | ||
75 | PipeLine | New Project4 | Person 4 | ||
76 | PipeLine | New Project4 | Person 10 | ||
77 | PipeLine | New Project5 | Person 6 | ||
78 | PipeLine | New Project5 | Person 7 | ||
79 | PipeLine | New Project6 | Person 20 | ||
Project allocation |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A78:B79 | Expression | =$A78="Pipeline" | text | NO |
A78:B79 | Expression | =$A78="Demand" | text | NO |
B78:B79 | Expression | =COUNTBLANK(B78) | text | NO |
A76:B77 | Expression | =$A76="Pipeline" | text | NO |
A76:B77 | Expression | =$A76="Demand" | text | NO |
B76:B77 | Expression | =COUNTBLANK(B76) | text | NO |
A59:C75,C76:C79 | Expression | =$A59="Pipeline" | text | NO |
A59:C75,C76:C79 | Expression | =$A59="Demand" | text | NO |
A57:H57 | Expression | =$B57="Awaiting PPM Upload" | text | NO |
C2:C53,B59:C75,C76:C79 | Expression | =COUNTBLANK(B2) | text | NO |
A2:C55 | Expression | =$B2="Awaiting PPM Upload" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C2:C53 | List | =IT_Team |
C59:C79 | List | =IT_Team |