Counting and sorting

New Contributor


I have a excel list containing some data in this form "0.0033MF 50V" (this is into a single cell), both MF and V values vary separately. I sorted them by both values and now i need to know, how many times each different value occurs (basically counting everything and sorting it again), what i found online gives me a number next to each value, but what i need is to count how many duplicates are from each value, without showing each duplicate, just one from each individual value, a match is when both MF and V are the same, and if either MF or V is different, it's a different thing, that needs to be counted separately.

Can someone help me? :)

8 Replies
Too difficult, if not impossible, to imagine! Please provide sample data and results.


I've attached a copy of the file, you'll know what i mean when you'll see it


please see if this makes sense. I was building this before I saw you had posted the file.

I have not looked at your file yet.

Cell in blue has an answer for the counting part. I am not sure what you mean with the sorting.

The two cells at the bottom show how I got each part of the formula.


Please let me know how it goes.



@THORs_Hand , another variant is to use FREQUENCY as explained here, array formula is like

=SUM(IF(FREQUENCY(IF(LEN(A2:A1000)>0,MATCH(A2:A1000,A2:A1000,0),""), IF(LEN(A2:A1000)>0,MATCH(A2:A1000,A2:A1000,0),""))>0,1))

and in attached file



As you'll see in the file, there are a lot of values, to be more specific, those are capacitors, MF is the capacitance, and V is the rated voltage. 

What i mean by sorting: after it counts how many of the same capacitors/values are there, makes another cell, where it says what value/capacitor it counted and how many are there/how many of the same data it found.

Maybe this will help, this is a parts list for a device i'm trying to restore, i need to see what and how many components i have to order. this is one of 14 of the same type of lists i need to sort in this way, this one being the shortest. there are too many components to sort manually in a shopping list.

@THORs_Hand , to extract unique values and sort them the formula is here https://www.myonlinetraininghub.com/excel-extract-a-unique-list

In the attached file, the formula in B2 is: 


The foregoing formula is the same as that which I posted as the last comment in the link provided by @Sergei Baklan , except that I replaced the standard lookup_value argument of 2 with the sexy PI() of @Detlef Lewin . 

Note that LeftRange is defined as: 


And it will be much more easier in new Excel with Dynamic arrays, just



Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies