Forum Discussion

Copper Contributor

Apr 26, 2017

Venn diagram or names in a list

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 attac...

Names lists venn.xlsx8 KB

Formulas & Functions

Need Help

Ian Tobin

Copper Contributor

May 02, 2017

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

SergeiBaklan

Diamond Contributor

May 02, 2017

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)
	),
	""
)}