Chart for daily events

[virtuosok]

Hi,
I have a set of subjects in a database, with their symptoms logged on a given date in one of the 4 columns. Outlining this below with some simplifications.
Now the first symptom (fever) is by far the most frequent and it is logged pretty much every day - sometimes even twice or more (see last 2 rows), if there has been a manual entry error / duplicate. The rest of the symptoms are hit and miss, they are logged very irregularly, and even if they were occurring on the same day as fever, they may be logged in a wrong row (see "cramps" on 03-Jan-2020 logged in the same row as "fever" on 02-Jan-2020).
Some days are symptom-free, so no entries on that day whatsoever (e.g. for the first subject below there's a week without any symptoms between 04 Jan and 10 Jan 20).
I'm trying to see whether I can build a clustered column or maybe some other chart type, which would have on horizontal axis a custom data range (let's say 01 Jan 20 - 01 Apr 20), and then 0...4 bars on each day, depending on whether some of the symptoms were logged that day. These bars should be colored differently so that it's readily visible whether there were a lot of symptom free days for that subject in a custom data range... is it possible with the dataset in question? I don't need different height for the bars if events were logged multiple times throughout a given day - as long as a symptom has been logged at least once on a given day, I just need a bar...

Subject	Fever​	Nausea​	Cramps​	Runny nose​
001	01-Jan-2020
001	02-Jan-2020		03-Jan-2020
001	03-Jan-2020
001	11-Jan-2020			11-Jan-2020
001	01-Feb-2020
002	01-Mar-2020	01-Mar-2020
002	02-Mar-2020	02-Mar-2020
002	03-Mar-2020
002	03-Mar-2020			05-Mar-2020

 

[EXCEL MAX]

Hello Virtuosok,
do you mean something like this?

 

[EXCEL MAX]

Or something like this...

 

[virtuosok]

Hi EXCEL MAX,
Second chart would be my ideal scenario. The only problem with it for my purposes is that it either displays 4 bars or none, whereas I would love to have 1 blue bar (only) on 1st Jan in line with the entry for fever, 1 blue bar on 2nd Jan, 1 blue and 1 green bar (cramps) on 3rd Jan etc... i.e. number of bars on a given day corresponding to the # and type of symptoms that day.
It's already great that days with no symptoms whatsoever are displayed correctly - if the condition above cannot be fulfilled, can you walk me through the creation of this chart as it's already halfway through my needs?

 

[EXCEL MAX]

This is created on Excel 2007, but let's try to go through...
Insert Clustered Column chart type.
Set your table from A1 to E10 as chart data source.
In the window for selecting cart data source click button to select new serias.
Delete series values textbox data.
Select data in the excel sheet from B1 to B10. Give name to the series.
Click OK. Click chart horizontal axis to be selected.
Now with right click on selected horizontal axis go to "Select Data". Click button "Edit".
Add data from B1 to B10 as axis label range. Click OK. Click OK again.
Resize chart horizontaly.
In the main menu go to layout and go to "Axes". Then "Primary Vertical Axes" select "None".
If we understand each other you need to got something like this...

If you want columns with few Sympthoms more try to create simmilar chart and set to transparent background.
Put chart one behind each other.

 

[virtuosok]

Thanks!
Is it possible to plot the other 3 symptoms on the same chart? Some of the days would have several bars whereas some would be "empty".

 

[EXCEL MAX]

Here is complete solution, but you need to use VBA to hide some data points....
Insert Clustered Column chart type.
In the "Select Data Source" window set "Chart data value" to empty.
Add four series but for the all Series values use same range with dates (=YourSheetName!$B$2:$B$10").
In the "Select Data Source" window click Edit button and set same range there. Click "Ok, Ok".
Resize chart horizontaly. Hide Primary vertical axes as I explaine before,
but now you need to set in the same menu "Show Axiswith Log Scale".
Rename chart and sheet.
Now you can use this macro to make all chart data points invisible if data source for this point is empty.
This is basic idea. I beleve you are capable to adapt the rest to your needs.

Sub CreateDailyEventChart()
   
    Dim varWS As Worksheet
    Dim varRange1 As Range, varRange2 As Range
   
    Application.ScreenUpdating = False
    Set varWS = Sheets("YourSheetName")
    Set varRange2 = varWS.Range("B2:E10")
    varWS.ChartObjects("Chart 1").Activate
    For Each varRange1 In varRange2
        If varRange1.Value = "" Then
            ActiveChart.SeriesCollection(varRange1.Column - 1). _
            Points(varRange1.Row - 1) _
            .Format.Fill.Transparency = 1
        End If
    Next
    Application.ScreenUpdating = True
   
End Sub

 

[virtuosok]

Thanks - and happy New Year

 

[EXCEL MAX]

I'm glad that we find solution.
Thanks to you.
Happy New Year too.?