Charting a Timeline with custom Y-Axis

**TyFrown** · 02-08-2021, 08:22 AM

Hello all

I hope that this isn't too much to ask in a forum, but I am looking for any level of approach to my issue. My question is related to charting a set of data against a timeline in a specific way, the jist of which is included below.

I have to convert some MATLAB code into VBA. Usually my methodology to do this is to develop each chart individually and manually, then convert to VBA. I am at a loss as to where to start with one specific implementation of the data.

The type of chart I need to make contains a timeline. This timeline is tied to individual actors taking certain actions, in that the chart will be plotted as time vs actor/action. The actions that each individual can take are 4 discrete types, but they are tied to the timeline and actor in such a way that I cannot get it to work. I have attached a jpeg of how the chart will display, as my explanation is likely not clear. Some other lesser limitations that may be sticking points in the future, but i dont care about right now:

I only need the top "X" number of actors, say 20, that take the most amount of actions.
I am not sure what future data would like but it appears that each actor can only take one action type
larger datasets as time goes on

Things I have tried on my real time data:

Creating pivot table with date as the x and actor as y then creating a pivot chart.
Creating pivot table with date as the x and actor as y then creating a scatter chart various ways.
Filtering the dataset and creating a milestone chart.

I guess if this cannot be answered a fallback would be for guidance on what type of chart to research, an approach to display in multiple charts, the most amount of data I can include in a single chart etc. (A fallback solution may be if we only filter by one action type, which I likewise cannot seem to get to work). I am unsure if this is a limitation on excel.

**JeteMc** · 02-12-2021, 02:06 PM

Things I have tried on my real time data:
Creating pivot table with date as the x and actor as y then creating a pivot chart.
Creating pivot table with date as the x and actor as y then creating a scatter chart various ways.
Filtering the dataset and creating a milestone chart.

Hello TyFrown and Welcome to Excel Forum.
While there is no guarantee that anyone will be able to produce your desired result, it may be helpful if you could include the graph that came closest to what you wanted along with an explanation of what changes need to be made so that the graph meets expectations.
Let us know if you have any questions.

**MrShorty** · 02-12-2021, 02:40 PM

I thought I had written a response for this one, but I guess I dreamed it.

What I see in this picture is basically a scatter chart. Each "Action" will be its own data series, and we will "replace" the numeric horizontal x axis with "Actor" names.

As with a lot of charting questions, most of the work is in the spreadsheet. Steps I see needing to happen (overview style, since I don't know what you will need help with):

1) We need a way to assign a horizontal number to each actor. I would probably use a lookup table/lookup function for this so that column D will contain a number determined by the actor name in column C. This column will be our Xvalues range for the scatter chart.
2) In columns E:??, we will populate the individual data series for each Action. Enter each Action in row 1 (E1, F1, and so on), then populate the table with a simple IF() function like =IF(E$1=$B2,$A2,NA()) -- Basically implementing this "conditional formatting of chart data" technique: https://peltiertech.com/conditional-...-excel-charts/
3) If you make sure D1 is empty, then you should be able to select D1:whatever and insert the scatter chart.
4) Then use a custom axis labels technique (similar to this: https://peltiertech.com/custom-axis-...n-excel-chart/ ) to put the actor names along the horizontal axis. If you use a lookup table in step 1, you can probably build the dummy series from the lookup table and not need another helper range.
5) Other chart formatting as needed.

Using those kinds of techniques, we should be able to build a chart like this.