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!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your profile. (y)
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
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
Thank you for the new formulas.
They work great
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,119
Messages
5,546,042
Members
410,722
Latest member
Jojo3008
Top