Need to create Venn diagram based on table

Not even sure if this is possible.

As shown on the attached. grid shows how many clients each salesperson deals with, and how many sales people each client deals with.

End User wants to show those relationships visually in Venn diagrams, as in the four "examples" on the attachment, but no idea how to "code" it, or even if is possible?

Employee Venn:
1. Select employee from Drop List
2. Code counts how many columns say "Yes" for that person, and creates a circle for each one.
3. Identifies which clients they are, and labels the circles with the relevant details - ideally would have different colours for different clients.
4. Put circles into relevant layout for the total of circles involved.

Client Venn
1. Select Client from Drop List
2. Code counts how many rows say "Yes" for that client, and creates a circle for each one.
3. Identifies which employees they are, and labels the circles with the relevant details
4. Puts the circles into the relevant layout for the total of circles involved.

Code would also have to accommodate "dynamic" ranges, as client and employee totals and details will change over time.

Any suggestions or pointers valued as ever.

Ochimus

since yours isnt a weighted veen chart...eg all circles are the same size

you could use the smartArt charts?
assuming 7 is your max...the charts lose too much detail after 7

For some reason i cant get smartArts to change colorthemes or styles properly

the basic premise works though
see attached file

Sub Employee()
'
    Dim shpHolder As Shape
    Dim objMain As SmartArt
    Dim objNode As SmartArtNode
    Dim cRow As Long
    Dim cCol As Long
    
    
    'Set Layout
    Set oSALayout = Application.SmartArtLayouts(110) '110>> Basic Venn
    
    'Set Colour
    Set oSAColors = Application.SmartArtColors(10) '10>> Colored Fill - Accent 1
    'Set Style
    Set oSAStyles = Application.SmartArtQuickStyles(1) '1>> Simple Fill
    'Colour and style doesnt seem to work properly
    
    For cRow = 2 To 8
        
        Set shpHolder = ActiveSheet.Shapes.AddSmartArt(oSALayout, oSAColors, oSAStyles)
        
        Set objMain = shpHolder.SmartArt
        
        'clear default nodes
        Do While objMain.AllNodes.Count > 1
            objMain.AllNodes(objMain.AllNodes.Count).Delete
        Loop
        
        Set objNode = objMain.AllNodes(1)
        cCol = 2
        Counter = 1
        For cCol = 2 To 6
            If Counter = 1 And UCase(Cells(cRow, cCol)) = "YES" Then
                objNode.TextFrame2.TextRange.Text = Cells(1, cCol).Text
                Counter = Counter + 1
                
            ElseIf UCase(Cells(cRow, cCol)) = "YES" Then
                Set objNode = objMain.AllNodes.Add
                objNode.TextFrame2.TextRange.Text = Cells(1, cCol).Text
                Counter = Counter + 1
            End If
        Next
        cCol = 2
    Next
    
    'next section is to clean up the SmartArts and position
    'at this point you can add labels if you want
    
    Dim numCol As Integer
    Dim w As Long
    Dim h As Long
    Dim TopPosition As Long
    Dim leftposition As Long
    
    numcols = 2 'number of columns for Shapes
    
    w = 300 'width of Shape
    h = 100 'Height of Shape
    
    'Change starting positions, if necessary
    TopPosition = 20
    leftposition = 350
    
    For i = 1 To ActiveSheet.Shapes.Count
        With ActiveSheet.Shapes(i)
            .Width = w
            .Height = h
            .Left = leftposition + ((i - 1) Mod numcols) * w
            .Top = TopPosition + Int((i - 1) / numcols) * h + 30
        
        End With
    Next i
End Sub

what you have suggested it no longer a Veen diagram
Veen is meant to overlap each other showing coorelative points

in your example is now hexagon cluster

which you can be replaced with basic radial chart - which you can also do with smart art
layout

83>> Basic Radial
it will require some tweaking of the VBA code as the employee is the parent and every client is then child of the parent

if i have time later today i give it a try to amend
its just a matter of getting the syntax

Sub Employeev2()
'
    Dim shpHolder As Shape
    Dim objMain As SmartArt
    Dim objNode As SmartArtNode
    Dim cRow As Long
    Dim cCol As Long
    
    Application.ScreenUpdating = False
    
    'Set Layout
    Set oSALayout = Application.SmartArtLayouts(83) '83>> Basic Radial
    
    For cRow = 2 To 8
        
        Set shpHolder = ActiveSheet.Shapes.AddSmartArt(oSALayout)
        
        Set objMain = shpHolder.SmartArt
        
        'clear default nodes, leaving base only
        Do While objMain.AllNodes.Count > 1
            objMain.AllNodes(objMain.AllNodes.Count).Delete
        Loop
        
        Set objNode = objMain.AllNodes(1)
        objNode.TextFrame2.TextRange.Text = Cells(cRow, 1).Text 'employee
        
        cCol = 2
        Counter = 1
        
        For cCol = 2 To 6
            If UCase(Cells(cRow, cCol)) = "YES" Then
               Set objNode = objMain.AllNodes.Add
                objNode.TextFrame2.TextRange.Text = Cells(1, cCol).Text
                Counter = Counter + 1
            End If
        Next
        cCol = 2
    Next
    
    'next section is to clean up the SmartArts and position
    'at this point you can add labels if you want
    Dim numCol As Integer
    Dim w As Long
    Dim h As Long
    Dim TopPosition As Long
    Dim leftposition As Long
    
    numcols = 2 'number of columns for Shapes
    
    w = 300 'width of Shape
    h = 200 'Height of Shape
    
    'Change starting positions, if necessary
    TopPosition = 30
    leftposition = 350
    
    For i = 1 To ActiveSheet.Shapes.Count
        With ActiveSheet.Shapes(i)
            .Width = w
            .Height = h
            .Left = leftposition + ((i - 1) Mod numcols) * w
            .Top = TopPosition + Int((i - 1) / numcols) * h
            .SmartArt.Color = Application.SmartArtColors(4) 'apply color theme
            .SmartArt.QuickStyle = Application.SmartArtQuickStyles(10) 'apply quick Style
        End With
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

code to do SmartArt with basic radial instead
also i found out how to do the color theme and quick style properly

use this below code to figure out what is what, comment out each

'https://www.experts-exchange.com/questions/28350113/smartArtLayouts-enumeration.html
'get the smartartlayout, color or style enumeration list into immediate window (Ctrl+G)

Sub getSA()
    Dim i As Integer
    On Error Resume Next
    Do
    i = i + 1
    'Debug.Print i & ">> " & Application.SmartArtLayouts(i).Name
    'Debug.Print i & ">> " & Application.SmartArtColors(i).Name
    Debug.Print i & ">> " & Application.SmartArtQuickStyles(i).Name
    
    Loop Until Err <> 0
End Sub