Forum Discussion
Counting and sorting
I've attached a copy of the file, you'll know what i mean when you'll see it
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 SergeiBaklan , 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))
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
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.
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.And it will be much more easier in new Excel with Dynamic arrays, just
=SORT(UNIQUE(FILTER(A2:A100,A2:A100>"")))
