Extract Unique, Sorted

ionelz

Board Regular
Joined
Jan 14, 2018
Messages
248
Office Version
  1. 365
Platform
  1. Windows
I want to Extract Unique of B, sorted by C
But I want max value of C
For example ase45 have in C value 2 and 3 so I want 3
Can we do this with one formula, without help columns ?
 

Attachments

  • Capture.JPG
    Capture.JPG
    61.6 KB · Views: 21

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Please Update Account Deails to we know you Use what Excel Version & OS.
For Example if you Use Office 365 then
Excel Formula:
=SORT(UNIQUE(FILTER(B2:B20,B2:B20<>"")))
Otherwise (without Sorting)
Excel Formula:
=INDEX($B$3:$B$20,MATCH(0,COUNTIF($E$1:E1,$B$3:$B$20),0))
 
Upvote 0
Or Use this Multi-Step Work for Excel version except Office 365:
Book1
ABCDEFGHIJK
1
2RankUnique CountExtractMAX C
3Abcde098aaaa12
4ase45211aabb221
5bb234113Blank CountAbcde0
6ss1233157akbk21
7aabb2218ase453
818bb2342
9ase45311ss1233
10zde4116zde45
11bb234213 
12zde4516 
13akbk2110 
14aaaa127 
1518 
1618 
1718 
1818 
1918
2018
21
Sheet1
Cell Formulas
RangeFormula
G3G3=SUM(--(FREQUENCY($E$3:$E$20,$E$3:$E$20)>0))-(G6>0)
G6G6=COUNTBLANK($B$3:$B$20)
J3:J10J3=MAXIFS($C$3:$C$20,$B$3:$B$20,I3)
I3:I14I3=IF(ROWS($I$3:I3)>$G$3,"",INDEX($B$3:$B$20,MATCH(MIN(IF(ISNA(MATCH($B$3:$B$20,$I$2:I2,0)),$E$3:$E$20)),$E$3:$E$20,0)))
I15:I18I15=IF(ROWS($I$1:I13)>11,"",INDEX($B$3:$B$20,MATCH(MIN(IF(ISNA(MATCH($B$3:$B$20,$I$1:I13,0)),$E$3:$E$20)),$E$3:$E$20,0)))
E3:E20E3=IF($B$3:$B$20="",ROWS($B$3:$B$20),MMULT(--(B3:$B$20>TRANSPOSE($B$3:$B$20)),ROW($B$3:$B$20)^0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Thank you so much for response. I have Office 365 with (SORT, SORTBY, FILTER, UNIQUE....)
So I am asking, if possible how can I extract data with one formula
I need UNIQUE from B and MAX from C, Sorted by C
 
Upvote 0
Then you can Use First Formula at Post #2. What is Problem With That?
For max of Column C Also use Last Column Formula at Post #3.
 
Upvote 0
I have Office 365
Please put that in your account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

1615980015718.png
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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