Creating a visual report that compares YOY by months for 1,000 clients

Do it for 1 client. But then use a dropdown box to select the client whose data are to be displayed.

Will you please attach a SMALL sample Excel workbook (not for 1000 clients... 2 or 3 only)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Originally Posted by Glenn Kennedy

Do it for 1 client. But then use a dropdown box to select the client whose data are to be displayed.

Will you please attach a SMALL sample Excel workbook (not for 1000 clients... 2 or 3 only)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hey Glenn,

Thanks for the advice. I have attached a sample sheet of how it is structured.

Originally Posted by BadlySpelledBuoy

If you do not need to compare all 1000 clients at a time, why not build a table to drive the chart that allows you to select a specific client from a list so you get a bespoke chart.

1000 of anything crammed into one chart would be awful to look at and unproductive in sense.

BSB

This sounds like a good idea. How would you create this?

The thing about the way our sheet is structured is both the 2015 and 2016 data are on the same row.

I've added another sheet to your sample workbook.

In cell D1 on this sheet is a data validation list to allow you to change the selected company name.
The table underneath this uses a 2D INDEX/MATCH/MATCH formula to look up the data from Sheet1 dependent on which company is selected.
The chart is driven by that table and auto adjusts when you select different company names.

You can of course format the chart however you like that the driver table can be hidden away if you don't wish to see it.

Any use to you?

BSB

Update: This worked brilliantly! +rep

How about using the INDEX formula you created for a data value which is irrelevant of the date?
For e.g: Let's say we got a new column called "Number of employees". How can we output the data too with the data validation option?