Forum Discussion

Ian Tobin's avatar
Ian Tobin
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 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

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 01, 2017

    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 Tobin's avatar
      Ian Tobin
      Copper Contributor
      May 02, 2017

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        May 02, 2017

        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

         

         

Resources