Results 1 to 5 of 5

Thread: Print Column of Values Present in all Each Other Column

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Print Column of Values Present in all Each Other Column

    Hello, all. I don't seem to be able to find this specific variant of a value-matching formula.

    I'm trying to output the list in column "ValueIsPresentInA-B-C", where it checks all of the other columns for values that must be present in each each of the others and then populates the column.

    The problem I'm trying to solve would use either of numbers or text.

    Any ideas on how this might work? Thank you for considering this problem.

    LIST-A LIST-B LIST-C ValueIsPresentInListA-B-C
    apples pears peaches pears
    guava lemon lime lemon
    watermelon blueberry blackberry apples
    pears apples pears dragonfruit
    lemon blackberry lemon
    peaches watermelon apples
    dragonfruit dragonfruit dragonfruit
    Last edited by cloobless; Jul 7th, 2019 at 10:53 PM.

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Print Column of Values Present in all Each Other Column

    Hi cloobless,

    Below should work:

    ABCD
    1LIST-ALIST-BLIST-CValueIsPresentInListA-B-C
    2applespearspeachesapples
    3guavalemonlimepears
    4watermelonblueberryblackberrylemon
    5pearsapplespearsdragonfruit
    6lemonblackberrylemon
    7peacheswatermelonapples
    8dragonfruitdragonfruitdragonfruit

    Sheet5



    Array Formulas
    CellFormula
    D2{=IFERROR(INDEX($A$2:$A$8,SMALL(IF(COUNTIF($B$2:$B$8,$A$2:$A$8)+COUNTIF($C$2:$C$8,$A$2:$A$8)=2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(D$2:D2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Copy the formula down to D8
    Last edited by Aryatect; Jul 8th, 2019 at 12:14 AM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Print Column of Values Present in all Each Other Column

    If the order is not important then this one will do the trick:

    ABCD
    1LIST-ALIST-BLIST-CValueIsPresentInListA-B-C
    2applespearspeachesapples
    3guavalemonlime
    4watermelonblueberryblackberry
    5pearsapplespearspears
    6lemonblackberrylemonlemon
    7peacheswatermelonapples
    8dragonfruitdragonfruitdragonfruitdragonfruit

    Sheet5



    Worksheet Formulas
    CellFormula
    D2=IF(COUNTIF($B$2:$B$8,$A2)+COUNTIF($C$2:$C$8,$A2)=2,$A2,"")

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print Column of Values Present in all Each Other Column

    Hello, Aryatect! I'm sorry for replying a few hours after your reply. I was working last night and was very tired and fell asleep.

    I hadn't thought about it, but yes the order is helpful and the solution works superbly. I'm able to collate a large list this way.

    Thank you for taking your time to help me find this solution. I appreciate it very much. Enjoy the day.

  5. #5
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Print Column of Values Present in all Each Other Column

    Glad could help!
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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