Apr 01 2019 05:56 AM - edited Apr 01 2019 05:57 AM
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? :)
Apr 01 2019 07:25 AM
Apr 01 2019 08:30 AM
I've attached a copy of the file, you'll know what i mean when you'll see it
Apr 01 2019 10:05 AM
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.
Apr 01 2019 10:21 AM
@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
Apr 01 2019 10:34 AM
@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.
Apr 01 2019 11:11 AM
@THORs_Hand , to extract unique values and sort them the formula is here https://www.myonlinetraininghub.com/excel-extract-a-unique-list
Apr 02 2019 02:32 AM
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))
Apr 02 2019 03:47 AM
And it will be much more easier in new Excel with Dynamic arrays, just
=SORT(UNIQUE(FILTER(A2:A100,A2:A100>"")))