Plotting two tables on a single chart

Can you perhaps provide more detail? Maybe some of the charts you have tried and what you did not like (or liked) about what you have tried?

With no other information, my first guess would be to select B1:F10 -> Insert -> desired chart type. Maybe move the 4 "number of members per council" series onto the secondary y axis. However, I would also guess that you have tried that. If so, perhaps you can explain what you don't like about that chart, which may help us better understand what you want to do.

I'm still not sure what you want to end up with. With this data arrangement, I can select A3:I6 and insert a chart, make sure that Excel is using "data series in columns", move some of the series onto the secondary y axis, and I will end up with a chart that shows council on the horizontal x axis, average revenue on one y axis, member # on the other y axis, and each data series represents a different year.

With the data arrangement in the OP, I can get a chart that shows year on the horizontal x axis, average revenue and member # on the 2 y axes, and each data series represents a different council.

In either case, the basic procedure for creating the combination chart is the same -- select the chart data -> insert chart -> switch row/column if needed -> move some data series to the secondary y axis -> change the chart type for some of the data series.

Maybe I will ask this. What do you want:
1) The horizontal x axis to show?
2) The primary vertical y axis to show?
3) The secondary vertical y axis to show?
4) Each data series to represent?