What is this chart? And, how do I make this chart with Excel?

[tjreeddoc]

All,

I am hoping someone can help me with a chart.

I would like to learn how to make a chart like the one below.

I am using Excel 2016 on running on a PC that runs Windows 10 Enterprise v1803

Questions:

• What type of chart is this?
• How do I make a chart like this with following:

I have 1000 Servers. I would like to update my chart with:

150 red dots that represent 150 Red Hat Servers

750 blue dots that represent 750 Windows Server 2016

100 green does that represent 100 Windows Server

Thank you,
tjreedoc

 

[Thosny]

Well TJ,
It's definitely in the realm of a perforation chart of some kind. The only way I was able to reproduce it was with some clever worksheet manipulation and some coding. It's manual for now but it could use some looping.... but for now.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim FontColor As Long
  Dim iSect As Range
  Set iSect = Intersect(Target, Range("WholeBigRange"))
 If Not iSect Is Nothing Then
  Cancel = True
  Select Case ActiveCell.Font.Color
    
    Case 16777215: FontColor = vbBlue ' if White make Blue
    Case 16711680: FontColor = vbRed  ' if Blue make Red
    Case 255: FontColor = vbGreen     ' if Red make Green
    Case Else: FontColor = vbWhite    ' or just make White
  End Select
  ActiveCell.Font.Color = FontColor

  End If
    
End Sub

Add a module with the following code:

Public Function CountColor(pRange1 As Range, pRange2 As Range) As Double

Application.Volatile
Dim rng As Range

For Each rng In pRange1
    If rng.Font.Color = pRange2.Font.Color Then
        CountColor = CountColor + 1
    End If
Next

End Function

As long as you stay within the boundaries of the (WholeBigRange), then you are free to move the dots (which are Windings letter "little L").

Doubleclick in the dots and they will change color for you. Now I can't imagine doing this for 1000 dots, so if someone can figure out a way to loop through the colored dots the right about of times (example: make them all white, then make X amt turn BLUE; then X amt turn GREEN and then X amount turn RED ) then that would work for you. It's a start.

 

[tjreeddoc]

Well TJ,
It's definitely in the realm of a perforation chart of some kind. The only way I was able to reproduce it was with some clever worksheet manipulation and some coding. It's manual for now but it could use some looping.... but for now.

View attachment 22138

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim FontColor As Long
  Dim iSect As Range
  Set iSect = Intersect(Target, Range("WholeBigRange"))
If Not iSect Is Nothing Then
  Cancel = True
  Select Case ActiveCell.Font.Color
   
    Case 16777215: FontColor = vbBlue ' if White make Blue
    Case 16711680: FontColor = vbRed  ' if Blue make Red
    Case 255: FontColor = vbGreen     ' if Red make Green
    Case Else: FontColor = vbWhite    ' or just make White
  End Select
  ActiveCell.Font.Color = FontColor

  End If
   
End Sub

Add a module with the following code:

Public Function CountColor(pRange1 As Range, pRange2 As Range) As Double

Application.Volatile
Dim rng As Range

For Each rng In pRange1
    If rng.Font.Color = pRange2.Font.Color Then
        CountColor = CountColor + 1
    End If
Next

End Function

As long as you stay within the boundaries of the (WholeBigRange), then you are free to move the dots (which are Windings letter "little L").

Doubleclick in the dots and they will change color for you. Now I can't imagine doing this for 1000 dots, so if someone can figure out a way to loop through the colored dots the right about of times (example: make them all white, then make X amt turn BLUE; then X amt turn GREEN and then X amount turn RED ) then that would work for you. It's a start.

Thosny,

Thank you!
Let me see about taking the next step with VBA!