Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Most frequent string in a column matched against a value in its ro
I currently have two columns with several text entries - one column is the
'Category' column and the other is the 'Name' column. I've been able to find out the most frequent string in Name by using the following =INDEX(Name;MODE(IF(Name<"";MATCH(Name;Name;0)))) However, what I want to know is the most frequent Name for a specific Category - for example, the most frequent Name string whenever the Category string is "Buyer". I tried doing this by using SUMPRODUCT and multiplying the Match(Name) value with (Category="Buyer") but that only returned #N/A (even as an array,formula) so either I got the syntax wrong or I was thinking along the wrong lines. Any good ideas? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Most frequent string in a column matched against a value in its ro
Array enter:
=INDEX(Name,MODE(IF(Name<"",IF(Category="Buyer",M ATCH(Name,Name,0))))) HTH, Bernie MS Excel MVP "Babymech" wrote in message ... I currently have two columns with several text entries - one column is the 'Category' column and the other is the 'Name' column. I've been able to find out the most frequent string in Name by using the following =INDEX(Name;MODE(IF(Name<"";MATCH(Name;Name;0)))) However, what I want to know is the most frequent Name for a specific Category - for example, the most frequent Name string whenever the Category string is "Buyer". I tried doing this by using SUMPRODUCT and multiplying the Match(Name) value with (Category="Buyer") but that only returned #N/A (even as an array,formula) so either I got the syntax wrong or I was thinking along the wrong lines. Any good ideas? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the least frequent value in an array | Excel Discussion (Misc queries) | |||
Return Numeric Value to their Matched Value Position in Single Column | Excel Worksheet Functions | |||
Find the most frequent date | Excel Worksheet Functions | |||
Return Title to matched column | New Users to Excel | |||
Finding the most frequent occurances | Excel Discussion (Misc queries) |