Venn diagram or names in a list

[Ian Tobin]

Hi

I would like to make a venn diagram using three circles to show the overlap between three lists of names.  I dont want to use the art version of it, I want it to populate from data.  I have attached a spreadsheet to show the lists.

If a venn diagram is too tricky or not possible I would like to be able to list which people are in each group please.

Thank you

Ian

[#2]

Hi Ian,

I've seen somewhere how to do Venn diagramm without built-in art tool, looks like a lot of manual work. In any case I may advise nothing here.

If to generate overlapping lists it could be two approaches (perhaps at least) - using Excel formulas or using Get & Transform (or Power Query in earlier versions of Excel).

For this exersise i transformed your data to Excel table. First approach is based on INDEX/MATCH tecnique, in particular use for 1-2 overlapping

=IFERROR(INDEX(Table1[2],MATCH(Table1[@1],Table1[2],0)),"")

which returns here names from first column which are met in second one. Simular is for other otherlapping. Not sure how final result shall looks like, perhaps some sorting/copy/paste work will require to polish it.

Second approach is much more flexible. Not sure how familiar you with G&T, here is just short idea. We create query from our source table, another query is its copy, after that we sequently join them by first-second, first-third and second-third column to generate tables with overlapping columns sorted alphabetically, finally merge them all and load result back to the Excel sheet.

Queries are simular, code for one of them

let
    
    // Join first and second columns using source table and its' copy
    Source = Table.NestedJoin(Table,{"1"},CopyOfTable,{"2"},"NewColumn",JoinKind.LeftOuter),

    // Pickup from resulting table the column with overlapping names
    ExpandNewColumn = Table.ExpandTableColumn(Source, "NewColumn", {"2"}, {"1-2"}),

    // For the sorting in ascending order replace null with symbol at the top of ASCII table
    ReplaceForSort = Table.ReplaceValue(ExpandNewColumn,null,"#(FFFF)",Replacer.ReplaceValue,{"1-2"}),
    SorteIt = Table.Sort(ReplaceForSort,{{"1-2", Order.Ascending}}),

    // Add Index to sorted table to combine all overlapping columns and in proper order
    AddIndex = Table.AddIndexColumn(SorteIt, "Index", 1, 1),

    RemoveUnusedColumns = Table.SelectColumns(AddIndex,{"1-2", "Index"}),

    ReplaceSortingCharBackToNull = Table.ReplaceValue(RemoveUnusedColumns,"#(FFFF)",null,Replacer.ReplaceValue,{"1-2"})
in
    ReplaceSortingCharBackToNull

Actually you may generate all above from UI, no coding is required.

The results for both variants

all formulas are in attached file

[#3]

Hi Sergi

Thank you very much for your reply.  I understand how the index and match works and it does exactly what I asked but I was also wondering if I can use the same formula to see which names are in columns 1, 2 ands 3?  Match only seems to work with two columns.

I can kind of see how the G and T thing works but I can work out how to edit it to add one that compares all three columns.

Thank you very much again

Ian

[#4]

Hi Ian,

You may use nested INDEX/MATCH. If you have column with "1-2" overlapping list, INDEX/MATCH values in this column on third column in your source table, you'll have "1-2-3" overlapping combination. If you don't need "1-2" use formula for it as search value in "1-2-3", i.e. final formula will be

=IFERROR(INDEX(Table1[3],MATCH(IFERROR(INDEX(Table1[2],MATCH(Table1[@1],Table1[2],0),0),""),Table1[3],0)),"")

Here

and in attached file

[#5]

Hi again

Sorry to be a pain but between reading your replies and reading up and what you posted I have cobbled together a formula which works well for the three columns, the formula is in cell M2 on the sheet I have attached, but I cant work out how to use the same formula for just two columns, im sure it should be easier than the three columns one but i cant get it to work! Do you have any ideas?

Thank you for all your help so far.

Ian

[#6]

Hi Ian,

Better use some formatting to sort out issues with long formulas. Your initial one is

=IFERROR(
	INDEX(Table1[1],
		MATCH(0,
			COUNTIF(N$1:$N1,Table1[1])+
				IF(
					IF(COUNTIF(Table1[2],Table1[1])>0,1,0)+
					IF(COUNTIF(Table1[3],Table1[1])>0,1,0)=2,
					0,1
				),
		0)
	),
	""
)

In nested IF remove second IF with COUNTIF which indicates if elements from columns 3 and 1 are overlapped, and first IF here compare with 1. Thus formula for 1-2 will be

{=IFERROR(
	INDEX(Table1[1],
		MATCH(0,
			COUNTIF(N$1:$N1,Table1[1])+
				IF(
					IF(COUNTIF(Table1[2],Table1[1])>0,1,0)=1,
					0,1
				),
		0)
	),
	""
)}

[#7]

Thank you very much Sergi!

I have been playing with these ideas for a while now and couldn't get them to work but all are now working beautifully, thank you!

[#8]

Thank you Sergei and IAN for this post.Can you please explain the same by using venn diagram in the excel and show how they both data and venn diagram interact dynamically.i mean when values in excel data changes then values in venn diagram should change.Is this possible.

[#9]

Thank you Sergei and IAN for this post.Can you please explain the same by using venn diagram in the excel and show how they both data and venn diagram interact dynamically.i mean when values in excel data changes then values in venn diagram should change.Is this possible.