E-Mail 'How to list unique distinct values sorted by frequency' To A Friend
Email a copy of 'How to list unique distinct values sorted by frequency' to a friend
Loading ... Loading ...14 Responses to “How to list unique distinct values sorted by frequency”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Your array formulas are very interesting.
But this fails if there is two or more values whith the same frequency.
Many thanks and best regards
Thanks for your comment! I have changed the formula and the attached excel file. The formula doesn´t work with blank cells.
Hi, very nice formula! I´m trying to do something like this, but I need to show one more column at the side of each unique element with the count of occurrences :-)
Fernando,
In the above example, try this formula in C9 copied down as far as necessary.
=COUNTIF(tbl, B9) + CTRL + SHIFT + ENTER
How can we do that in Excel 2010? without VBA or PowerTools or PowerPivot etc. but only using worksheet formulas. I can do it in VBA. I am asking because it's very hard to figure out using only worksheet formulas. Thanks in advance. Since dates were not shown, I would like to record my request as made on 07MAY2023.
T S,
This article will probably show you how: Unique distinct values sorted based on frequency if you can rearrange your values to a single column.
Hi, the formula given is missing a pair of parentheses thus returning only UNIQUE values not UNIQUE and DISTINCT values.
But your explanation on calculation steps contains the correct formula.
Thank you for sharing this formula.
https://i.imgur.com/fgxAyKd.png
T S,
thank you for telling me. I hope I got it right now.
Hi, I took the liberty of editing the formula presented here into one that does not require CSE.
I did it because most beginners have trouble pressing that key combo.
My editition might affect performance but if it's a very large data set (column), I don't think much will be affected on most modern computers.
I think the basic principle is still the same as the one already shared here.
=IF(SUMPRODUCT(1*(COUNTIF(T$2:$T2, $B$3:$B$12)=0))=0,"",INDEX($B$3:$B$12,MATCH(AGGREGATE(14,6,COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(T$2:$T2, $B$3:$B$12)=0),1),INDEX(COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(T$2:$T2, $B$3:$B$12)=0),0),0)))
The above formula in question can be found here:https://imgur.com/a/76rvFqP
Comment Date:17MAY2023
The following formula is for extracting only Unique values:
=IFERROR(INDEX($B$3:$B$12,MATCH(1,INDEX(COUNTIF($B$3:$B$12,$B$3:$B$12)*(COUNTIF(Y$2:Y2,$B$3:$B$12)=0),0),0)),"")
Above formula in action can be found here:https://imgur.com/5KLglwU
Dated:17MAY2023
The following formula is for extraction of Duplicated values only.
=IFERROR(INDEX($B$3:$B$12,MATCH(AGGREGATE(14,6,COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),1),INDEX(COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),0),0),MATCH(1,INDEX((COUNTIF($B$3:$B$12, $B$3:$B$12)>1)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),0),0)>0),"")
Above formula in action can be found here:
https://imgur.com/qCSMZhG
T S,
thank you for posting your Excel formulas.
I don't have the luxury of rearranging to a single column.
So, I modified your formula at https://www.get-digital-help.com/unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel/ to become like https://imgur.com/jCvNhdR .
Further explanation can be found at https://stackoverflow.com/questions/76405604/excel-formula-to-extract-a-sorted-list-of-topn-unique-distinct-string-text-val/76405605#76405605 .
This reply was posted on 05JUN2023 though I finished my formula 5 days after you replied.
Thank you for sharing your answer in the first place.
Allow me to share your great website with others.
This site contains many helpful articles like a treasure chest in a dungeon!
I do appreciate your kind efforts.
Thanks again.
Tragic Shadow,
thank you.