Find top occurrence in a column with multiple values in a cell

[kaushik03]

Hi all,

I am trying to find the top occurrence in a column with multiple values in a cell. Let me explain what I want.

In the attached file(with sample data),

1. Column B contains some entries which I am trying to find the top occurrence from, basis the criteria in column A and C

2. Let me give an example:

a) In column A, for NS and in column C for GnMed, I have the following occurrence of data in Column B:

> Deva 3 time, Cer 2 times, Ron, Asu, Tri and Raf 1 time each

3) Now in Column D, I want a formula which would populate me the result(for the selection of NS and Genmed) in descending order as follows:
Deva
Cer
Ron
Asu,
Tri
Raf

So basically I will have drop downs for Col A(NS, Ophtha) and col C (GenMed, Onco) unique entries. Selecting the criteria in these two drop downs, Col B values should be populated in col D in descending order (basis number of times Col B values repeats in the specified criteria range).

Challenge:
1)I cant change the format of data in column B (It contains multiple entries in same cell and it has to be like this)

If not formula, then VA based solution would also work for me.

Looking forward to have the solution.

Regards,
Kaushik

 

[kaushik03]

can someone please address this problem?

 

[Paijo]

Its difficult to address your expected results, Why you need Deva with occurences 3 time? When you have Ron Deva and Deva Asu in your data?

 

[XOR LX]

Hi.

You say that you have a column with "multiple values in a cell", yet, based on your attachment, we could safely replace the word "multiple" with "two".

Or have you perhaps not included a realistic dataset here? Could there in fact be any number of names within a given cell?

Also, are entries within a given cell always separated by a single linebreak?

Regards