0

I have a list of values in column A. Some of the values occur once, some occur twice, some occur more than 2 times. In column C, I would like to only list the values from column A that are repeated more than once, and I would like to sort them from highest to lowest by number of occurrences.

I was able to develop a formula that lists the duplicates, and I can easily use CountIf to list the number of occurrences. Unfortunately, the formula for listing the duplicates is an array formula, so I can't sort it based on the number of occurrences.

Is there any other solution?

  • 1
    It would be useful to have that formula. Can you please edit your question to include it as well as some sample data? – cybernetic.nomad Jul 3 at 20:17
  • My simple suggestion is, edit your question and share all the Formula & Functions you have tried on the data set along with Source data and the expected answer since the confusing part is that,, pull unique values or all have occurred more than once!! – Rajesh S Jul 4 at 6:50
0

This iterates the range an does the count of duplicates and returns the value:

=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW($A$1:$A$10)/((COUNTIF(A:A,$A$1:$A$10)=AGGREGATE(14,7,COUNTIF(A:A,$A$1:$A$10)/(COUNTIF($D$1:D1,$A$1:$A$10)=0),1))*(COUNTIF($D$1:D1,$A$1:$A$10)=0)*(COUNTIF(A:A,$A$1:$A$10)>1)),1)),"")

One would need to change all the $A$1:$A$10 to that of the actual range.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.