Showing the most, second most, and third most occurring values

Question

Which formula shows unique (text) values of Column A, and the corresponding most occurring values (text) from Column B and sorts them on degree of appearance over different columns?

I already know how to show only the first most occurring values, but not the second, third and so on.

The formula which only shows the first most occurring values:

=ArrayFormula(VLOOKUP(UNIQUE(FILTER(A:A;A:A<>""));QUERY({A:B\A:A};"select Col1, Col2, count(Col3) where Col1 <> '' group by Col1, Col2 order by count(Col3) desc");{1\2};0))

Test Sheet

Answer 1

Try a combination of QUERY and INDEX pulled across

=iferror(index(query($A$1:$C$100, "select B,count(C) where A contains '"&$E2&"' group by B order by count(C) desc",1) ,columns($AA:AB),1),"")

where the media type is in E2.

Answer 2

In addition, for a single cell formula, try query()

=query(A:B; "select A, count(B) where A<>'' group by A label count(B) 'Frequency'"; 1)

Answer 3

EDITED (Following clarification of the question and some improvements to ensure the answer stands alone, without reference to external spreadsheets)

I use a combination of "FILTER()", "UNIQUE()" and "SORT()", in conjunction with the calculated frequencies (determined using COUNTIFS). See the sample sheet below, taken from yours.

It's not a single cell formula, but it's simple and easy to understand.

The spreadsheet is structured as below:

Channels are in column A, the associated media are in column B, and the frequency of a given (Channel, Media) pair is in Column C given by, (in C2, for example)

=countifs($B$2:$B;"=" &B2;$A$2:$A;"=" & A2)

Then make a list of unique media using =UNIQUE(B2:B). Adjacent to a given unique medium (say, in cell F13), I then determine all the pairings related to that chosen medium and create an array ordered by the most frequent such pairing:

=transpose(unique(transpose(array_constrain(transpose(sort(filter($A$2:$C;$B$2:$B=F13);3;FALSE));1;10))))

This works by filtering out all pairs (with the pair frequencies) for the chosen medium (selected in F13), sorts the list by the pair frequency (i.e., the 1st, 2nd, 3rd most recurring media), trims the array to display only media, and then removes repeated entries (since we have one repeated entry each time a given pairing is repeated, i.e., seen more than more than once). Transpose is used because Unique didn't work with a row array, only a column array.

The formula works for any additional channels and media added to columns A and B respectively.

The "10" un the formula refers to the number of columns retained during array_constrain() and can be made arbitrarily high.

Modifications to Original Posting: In order to make this work, I had to swap the media and channels columns with each other in the original answer, and then add a column C which was the frequency of that pairing, which I later used to sort the 1st, 2nd, etc.. most popular channels.