Formula Help

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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

Thank you in advance!!
 

Some videos you may like

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
Joined
May 8, 2018
Messages
1,279
Office Version
  1. 365
  2. 2010
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
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,279
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
Thanks you so much for all your help.
Yes helpers are no problem at all.
Thank you
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 8, 2018
Messages
1,279
Office Version
  1. 365
  2. 2010
Your profile says "365 and 2010"
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
I stopped using 365. Sorry I did not update my profile.
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
Can a Function be created that will closely resemble the SortBy formula in 365?
Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,030
Members
410,721
Latest member
adi772
Top