# Formula Help

#### ststern45

##### Well-known Member
Hello,

I have a range of numbers in each cell that range from 0 through 9
Example:
A1=2
A2=4
A3=3
A4=5
A5=3
A6=5
A7=1
A8=6
A9=3
A10=4

I am looking to take the values in cell range A1:A10 and place the values in order as follows:
Values that appear the most to least.
So in the example above the result would be:
3334455216
The above calculations will be in cell range B1:B10
Next I want to take the 3334455216 and remove the duplicates.
So the result would be 345216
This would go in cell range C1:C6.

Example 1: Above
2435351634 (Cell range A1:A10) User entered values
3334455216 (Cell range B1:B10) Arrange in order-duplicate values that appear the highest 1st
345216 (Cell range C1:C6) Remove Duplicates from B1:B10

Example 2:
254925582 (Cell range A1:A9) User entered values
222555498 (Cell range B1:B9) Arrange in order-duplicate values that appear the highest 1st
25498 (Cell range C1:C5) Remove duplicates from B1:B9

Example 3:
4165323 (Cell range A1:A7) User entered values
3341652 (Cell range B1:B7) Arrange in order-duplicate values that appear the highest 1st
341652 (Cell range C1:C6) Remove duplicate from B1:B7

#### Fluff

##### MrExcel MVP, Moderator
I stopped using 365. Sorry I did not update my profile.
Can you please do so, that way members do not waste their time creating a formula that won't work. Thanks

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Fluff

##### MrExcel MVP, Moderator
With a couple of helpers
+Fluff v2.xlsm
ABCDE
1
221333
342334
433335
552246
633251
752252
81124
96116
103311
114212
Main
Cell Formulas
RangeFormula
B2:B11B2=COUNTIFS(A:A,A2)
C2:C11C2=AGGREGATE(14,6,\$B\$2:\$B\$11,ROWS(C\$2:C2))
D2:D11D2=INDEX(\$A\$2:\$A\$11,AGGREGATE(14,6,(ROW(\$A\$2:\$A\$11)-ROW(A\$2)+1)/(\$B\$2:\$B\$11=LARGE(\$B\$2:\$B\$11,ROWS(D\$2:D2))),COUNTIFS(C\$2:C2,C2)))
E2:E11E2=IFERROR(INDEX(\$D\$2:\$D\$11,AGGREGATE(15,6,(ROW(\$D\$2:\$D\$11)-ROW(\$D\$2)+1)/(ISNA(MATCH(\$D\$2:\$D\$11,E\$1:E1,0))),1)),"")

#### ststern45

##### Well-known Member
Thank you for the new formulas.
They work great

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
1
Views
74
Replies
12
Views
198
Replies
4
Views
103
Replies
4
Views
94
Replies
4
Views
379