Forum Discussion
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
- 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
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))
