Populate tables by category

MK0708,

See attached which ranks your TV programmes by channel and viewers. I use two array formulas, the first finds the programmes that are on specific channel ($A$1), if that condition is true is pulls the audience figures for that programme. I then use the LARGE function to give the biggest first etc

{=IFERROR(LARGE(IF($A$1=data!$B:$B,data!$C:$C),ROW(tables!B1)),"")}

The programme name is then pulled by matching the channel with the viewing figure (A$1&B3) to the relevant columns in the data table to give the row and then using the index function to pull the value of the row

{=IFERROR(INDEX(data!$A:$A,MATCH(A$1&B3,data!$B:$B&data!$C:$C,0)),"")}

I have used the references for all the columns ($A:$A, $B:$B, $C:$C) which has the advantage that you can fill in as many programmes as you want on the data table but it may make it slower than if you restricted the ranges to 100. You can play about to see what works for you.

Regards

David

mk0708,

I would need to see the spreadsheet you are looking at to diagnose your problem, any chance you can attach (remove any confidential data first)!

Regards

David

mk0708,

On the Channel A table you were correct in that the DIV/0 error was creating the problem. I have added an additional IFERROR to replace the DIV/0 with a FALSE and now it works. See attached

A tip to debug - I changed the ranges to B1:B20 etc and then if you highlight each section of the formula and press F9 it evaluates, <ctrl-z> to go back. In this way I could see what was going wrong,

On Channel B you had another problem, the LARGE (xxx,n) function returns the nth largest from a list of values. I used the Row(b1) to generate a 1, when you copy this down you get Row(b2) in the next to give a 2, Row(B3) in the next to get a 3. So when you start the ranked list for Channel B you need to reset this to B1 for the first so it pulls the largest, you cannot just copy this down from the previous table.

Another thought - it might be easier to do in a pivot table, no complicated formulas to think about. I have put one in the attached.

Regards

David