HI experts,
I have a excel sheet with data layout as following in two columns
A1 D
A1 D
A1 D
A2 D
A2 D
A3 D
A3 D
A4 D
What i need is to write a formula which should take "D" as criteria and give the value in Column A which has the maximum occurences, then the item with 2nd maximum no. of occurences and so on.
The output should be
A1 D
A2 D
A3 D
A4 D
Currently I am stucked.
I would appreciate any I would get from your side.
Thanks
Last edited by adyan76; 02-02-2012 at 03:36 AM.
Its better you attach a file with the sample data. Would there only be "D" in the 2nd column or other data as well?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There is just D in the second column.
Maybe this.. There must be something simplier but for now:
Thank you very very much. This is exactly what I was looking for.
Just a question here for my limited mind.
How would I adjust the formula if I have not just D in column 2?.
Thanks again
Formula would be same only it would return first letter from a table. So in case you have:
A1 B
A1 C
A1 D
A2 D
A2 D
A3 D
A3 D
A4 D
It owuld return B for A1 and D for other
Thanks again for the clarification.
What if I want to adjust the formula to just consider one value in column2 i.e D or E.
PHP Code:=IF(MAX(COUNTIF($A$1:$A$100,IF(ISNA(MATCH($A$1:$A$100,F$7:F8,0)),$A$1:$A$100&"")))=0,"",INDEX($A$1:$A$100,MATCH(MAX(COUNTIF($A$1:$A$100,IF(ISNA(MATCH($A$1:$A$100,F$7:F8,0)),$A$1:$A$100&""))),COUNTIF($A$1:$A$100,IF(ISNA(MATCH($A$1:$A$100,F$7:F8,0)),$A$1:$A$100&"")),0)))
So, to be clear, from mine above example you need output like this:
A1 B
A1 C
A1 D
A2 D
A3 D
A4 D
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks