Forum Discussion
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 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
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
- Ian TobinCopper Contributor
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
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