+ Reply to Thread
Results 1 to 3 of 3

Check to see if two inputs share the same data

  1. #1
    Registered User
    Join Date
    09-28-2020
    Location
    UK
    MS-Off Ver
    2013
    Posts
    21

    Check to see if two inputs share the same data

    I've attached a spreadsheet that has a data table in cells B3:E6. On the left side is a list of categories, from 1 to 3. The data to the right of this list (in cells C4:E6) is data based on these categories. For example, category 1 has the following data: 'circle', 'square' and 'triangle'. I would like to create something, as shown in examples A and B, where I can input these categories ranging from 1 to 3 in the green-filled areas, and an output is automatically generated in the orange-filled areas. This output will be based on what data is shared by the categories selected.

    In example A, I have inputs '1' and '2'. If you look at the data table, the word 'triangle' is shared between 1 and 2 (cells E4 and E5). This is the only output.
    In example B, I have inputs '1', '2', and '3'. If you look at the data table, the words 'circle' and 'square' are shared between '1' and '3' (C4:D4 and C6:D6). So, the output has both of these shapes for '1' and '3'. The only other output is 'triangle', as this is shared between '1' and '2'.

    Could somebody please help me with a formula that can generate said outputs?

    Thank you.

    Cross link: https://www.mrexcel.com/board/thread...-data.1224575/
    Attached Files Attached Files
    Last edited by ExcelDropper; 12-13-2022 at 04:00 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,925

    Re: Check to see if two inputs share the same data

    I am positive I could do this with VB, but the challenge is to do it with formulas. Perhaps the following will inspire someone smarter than me.

    The Table is a truth table. So, if you look at column 1, it tells you that triangle is common to 1 and 2 and that both circle and square are common to 1 and 3. If you look at column 2, it tells you triangle is common with 1 but nothing is common with 3. If you look at column 3, circle and square are common with 1 and nothing is common with 2.

    The truth is out there, but how do we create the table (there are formulas in the table) and how do we "collect" the intersections without VB?
    Attached Images Attached Images
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Check to see if two inputs share the same data

    I'm not sure about functions in 2013 so I use & for connecting strings in formula.

    Helper 1 check duplicate of words.

    G4
    =COUNTIF($C$4:$E$6,C4)

    Helper 2 list words that have multiples occur.

    K4
    =IF(G4>1," "&C4,"")

    Output join them all

    O4
    =SUBSTITUTE(SUBSTITUTE(TRIM(K4&L4&M4)," ",","),",",", ")

    Regards,
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 02-17-2021, 08:48 AM
  2. Macro to un-share then re-share OR spell check a hsared workbook Macro?
    By yetibeard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2018, 06:32 PM
  3. Replies: 15
    Last Post: 11-06-2017, 11:44 AM
  4. USER inputs Data in data form...Data goes to Cell
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-24-2013, 03:40 PM
  5. Share macro through two different networks/ Share through Internet.
    By Saji P in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-02-2013, 05:30 AM
  6. Replies: 4
    Last Post: 02-02-2011, 11:51 AM
  7. Replies: 2
    Last Post: 02-27-2009, 02:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1