Sorting Unique List of Names in order

P

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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
Resource Allocation Tool - RAT - Example.xlsx
FGH
1ProjectPipeline ProjectPeople
2Project 1New Project2Person 1
3Project 2New Project3Person 2
4Project 3New Project4Person 3
5Project 8New Project5Person 20
6Project 9New Project6Person 13
7Project 10NewDemandProject1Person 14
8Project 11NewDemandProject2Person 8
9Project 13NewDemandProject3Person 6
10Project 14NewDemandProject4Person 15
11Project 15NewProject1Person 10
12Project 16Person 26
13Project 17Person 4
14Project 18Person 7
15Project 19Person 11
16Project 20Person 9
17Project 21Person 23
18Project 22Person 18
19Project 23Person 5
20Project 24Person 27
21Project 25Person 25
22Person 12
23Person 17
24Person 16
Lists
Cell Formulas
RangeFormula
F2:F21F2=UNIQUE(TblProjAlloc[PROJECT NAME])
G2:G11G2=SORT(UNIQUE(Table4[PROJECT NAME]))
H2:H24H2=UNIQUE(TblProjAlloc[WHO])
Dynamic array formulas.


Data Source
Resource Allocation Tool - RAT - Example.xlsx
ABC
1PROJECT NUMBERPROJECT NAMEWHO
2190105Project 1Person 1
3190105Project 1Person 2
4190165Project 2Person 3
5190165Project 2Person 20
6190165Project 2Person 13
7190165Project 2Person 14
8200066Project 3Person 8
9200067Project 8Person 6
10200067Project 8Person 15
11200067Project 8Person 3
12200067Project 8Person 10
13200067Project 8Person 26
14200067Project 8Person 1
15200078Project 9Person 4
16200078Project 9Person 10
17200087Project 10Person 20
18210001Project 11Person 1
19210001Project 11Person 15
20210004Project 13Person 7
21210004Project 13Person 11
22210004Project 13Person 9
23210004Project 13Person 23
24210007Project 14Person 1
25210007Project 14Person 18
26210007Project 14Person 26
27210007Project 14Person 5
28210007Project 14Person 27
29210007Project 14Person 9
30210007Project 14Person 25
31210007Project 14Person 4
32210007Project 14Person 8
33210007Project 14Person 23
34210007Project 14Person 12
35210008Project 15Person 3
36210008Project 15Person 17
37210008Project 15Person 6
38210008Project 15Person 12
39210010Project 16Person 2
40210015Project 17Person 18
41210022Project 18Person 15
42210024Project 19Person 15
43210028Project 20Person 6
44210028Project 20Person 15
45210036Project 21Person 18
46210037Project 22Person 16
47210044Project 23Person 3
48210044Project 23Person 17
49210055Project 24Person 18
50210062Project 25Person 13
51210062Project 25Person 23
52210062Project 25Person 10
53210062Project 25Person 27
54
55
56
57Demand / Pipeline Projects (Not yet approved)
58StagePROJECT NAMEWHO
59DemandNewDemandProject1Person 1
60DemandNewDemandProject1Person 2
61DemandNewDemandProject1Person 4
62DemandNewDemandProject2Person 1
63DemandNewDemandProject2Person 2
64DemandNewDemandProject2Person 3
65DemandNewDemandProject3Person 2
66DemandNewDemandProject3Person 4
67DemandNewDemandProject3Person 6
68DemandNewDemandProject4Person 1
69DemandNewDemandProject4Person 10
70DemandNewDemandProject4Person 11
71PipeLineNewProject1Person 1
72PipeLineNewProject1Person 4
73PipeLineNew Project2Person 2
74PipeLineNew Project3Person 7
75PipeLineNew Project4Person 4
76PipeLineNew Project4Person 10
77PipeLineNew Project5Person 6
78PipeLineNew Project5Person 7
79PipeLineNew Project6Person 20
Project allocation
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A78:B79Expression=$A78="Pipeline"textNO
A78:B79Expression=$A78="Demand"textNO
B78:B79Expression=COUNTBLANK(B78)textNO
A76:B77Expression=$A76="Pipeline"textNO
A76:B77Expression=$A76="Demand"textNO
B76:B77Expression=COUNTBLANK(B76)textNO
A59:C75,C76:C79Expression=$A59="Pipeline"textNO
A59:C75,C76:C79Expression=$A59="Demand"textNO
A57:H57Expression=$B57="Awaiting PPM Upload"textNO
C2:C53,B59:C75,C76:C79Expression=COUNTBLANK(B2)textNO
A2:C55Expression=$B2="Awaiting PPM Upload"textNO
Cells with Data Validation
CellAllowCriteria
C2:C53List=IT_Team
C59:C79List=IT_Team
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You must log in or register to reply here.

Similar threads

P
  • Question
Combining two data tables in Sumifs Project Summary Report
Replies
1
Views
356
PaulyK
P
P
  • Solved
SUMIFS incorrect results when adding rows or sorting columns
Replies
3
Views
384
Fluff
Fluff

Forum statistics

Threads
1,215,063
Messages
6,122,935
Members
449,094
Latest member
teemeren

Share this page

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