 Highlighted
New Contributor

# Counting and sorting

Hello,

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
Highlighted

# Re: Counting and sorting

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

# Re: Counting and sorting

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

Highlighted

# Re: Counting and sorting

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.

Highlighted

# Re: Counting and sorting

@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

Highlighted

# Re: Counting and sorting

@Celia_Alves

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.

Highlighted

# Re: Counting and sorting

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

Highlighted

# Re: Counting and sorting

In the attached file, the formula in B2 is:

=IFERROR(LOOKUP(PI(),1/(COUNTIF(LeftRange,">="&LeftRange)=MAX(INDEX(
COUNTIF(LeftRange,">="&LeftRange)*(COUNTIF(B\$1:B1,LeftRange)=0),0))),
LeftRange),"")

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:

=Sheet1!A\$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A))

Highlighted

# Re: Counting and sorting

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

`=SORT(UNIQUE(FILTER(A2:A100,A2:A100>"")))`