# 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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### kweaver

##### Well-known Member
If the numbers are supposed to be in order, how does "21" come after the digits before? Should 1 be first, then 2, then 333, etc.?

#### ststern45

##### Well-known Member
They are arranged in the following sequence:
Example 1:
2435351634 (Cell range A1:A10) User entered values
3334455216 (Cell range B1:B10)
345216 (Cell range C1:C6) Remove Duplicates from B1:B10

2435351634 look for numbers that appear the most as they appear in the string 2435351634
333
This leaves
2455164
44
This leaves
25516
55
This leaves
216
Final Calculation:
333 44 55 216 (Without spaces)This would go in in cell range B1:B10

Next remove duplicate values and these would go in cell range C1:C10
3 4 5 2 1 6 (without spaces)

Thank you

#### ststern45

##### Well-known Member
Example 2:
254925582 (Cell range A1:A9)
222555498 (Cell range B1:B9)
25498 (Cell range C1:C5)

254925582

222 555 498

25498

Example 3:
4165323 (Cell range A1:A7)
3341652 (Cell range B1:B7)
341652 (Cell range C1:C6)

33 41652

341652

#### kweaver

##### Well-known Member

Could any helper columns be used? I haven't tried to combine them. There might a much slicker way that someone else will find.

Book2
ABCDE
12 300
241411
332521
45333133
5344242
6555652
71661
86 70
93 80
104 90
11100
120
Sheet1
Cell Formulas
RangeFormula
C1:C10C1=LEFT(SORTBY(B1:B10,LEN(B1:B10),-1),1)
E1:E12E1=FREQUENCY(A1:A10,D1:D11)
B1:B10B1=IF(0<>E1,REPT(D1,E1),"")
Dynamic array formulas.

#### ststern45

##### Well-known Member
Thanks you so much for all your help.
Yes helpers are no problem at all.
Thank you

#### ststern45

##### Well-known Member

Thank you again.
The only problem and this is my problem is that I only have Excel 2010
The SortBy Formula will not work in Excel 2010

#### kweaver

##### Well-known Member
Your profile says "365 and 2010"

#### ststern45

##### Well-known Member
I stopped using 365. Sorry I did not update my profile.

#### ststern45

##### Well-known Member
Can a Function be created that will closely resemble the SortBy formula in 365?
Thank you.

Replies
1
Views
66
Replies
12
Views
186
Replies
4
Views
101
Replies
4
Views
94
Replies
4
Views
378