Copying data to another sheet in a particular format and Auto-adjustment

[branston]

Hi

I have some data (sheet1) that is in 4 groups that I am copying on to another sheet (sheet2) in a very specific format, namely a Venn diagram format.

I have and can copy the data over by using macros and 'Named Ranges' and then placing each group in the correct section of the diagram. It's a bit long winded method but works.

The issue I am having is that the groups can become very long which means I have to MANUALLY adjust the Venn diagram and make sure enough Named Ranges / space exists before populating each 3x4 rectangle. It can become a tiresome process.

What I would like to know is :

1. Is it possible (in Excel) to create a Venn that auto-adjusts in size depending the amount of Names in each group?
2. Is there a better way / solution / software to do this e.g. Power BI?

I have included a dummy sheet (without macros) to illustrate the problem. Hopefully it's self explanatory. Can anyone help?

TIA

[Kenneth Hobs]

This is tough for me as I don't know your data relationships and I usually deal with numbers.

One way might be able to establish a 1:1:1 relationship and then do a ratio correction as size increases or decreases from the model.

Maybe use Insert > SmartArt > Relationships > Basic Venn or such. Then Shift+select the shapes and then SmartArt Design > Convert to Shapes. Then ungroup the shapes. Name the Shapes and insert named textbox controls in the center. That is the model. Then copy worksheet and by a ratio correction, increase/decrease shape sizes to fit your data. Then the text can be added to the textboxes.

Powerpoint has a way to do similar but also a way to make the intersecting shapes to split out. Maybe that could be an advanced version for later...

[branston]

Thanks Ken.