+ Reply to Thread
Results 1 to 9 of 9

Return Only Data That Appears in Three (Or More) Columns

  1. #1
    Registered User
    Join Date
    10-09-2019
    Location
    Los Angeles, CA
    MS-Off Ver
    2017
    Posts
    14

    Return Only Data That Appears in Three (Or More) Columns

    Hi,

    I'm looking for help finding a formula that would return data that shows up in multiple columns. In the attached sheet, I have three different columns with different bakeries. I want it to return only the bakeries that are in all three of the columns - in this case, only Brenda's Bake Shop would be listed.

    Thanks!
    Attached Files Attached Files

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

    Re: Return Only Data That Appears in Three (Or More) Columns

    H5
    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/(1/((B:B<>"")*(COUNTIF(D:D,B:B)>0)*(COUNTIF(F:F,B:B)>0))),ROWS(H$4:H4))),"")


    But this formula is take time too long.
    If you can limited range area shorter it will be better as.

    H5
    =IFERROR(INDEX(B$1:B$15,AGGREGATE(15,6,ROW(B$1:B$15)/(1/((B$1:B$15<>"")*(COUNTIF(D$1:D$15,B$1:B$15)>0)*(COUNTIF(F$1:F$15,B$1:B$15)>0))),ROWS(H$4:H4))),"")

    $1 must start from $1
    $15 can change for your last row of data.

    Regards.

  3. #3
    Registered User
    Join Date
    10-09-2019
    Location
    Los Angeles, CA
    MS-Off Ver
    2017
    Posts
    14

    Re: Return Only Data That Appears in Three (Or More) Columns

    Hi, thank you so much for your help but that's not returning anything for me. Anyone else have any ideas?

    Thanks!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Return Only Data That Appears in Three (Or More) Columns

    I tested both of menem's formulas in cells H5:H8 and they both return Brenda's Bake Shop in cell H5 while leaving H6:H8 blank.
    The AGGREGATE function is supported by versions of Excel 2010 and later and since your profile shows 2017 I don't know why it wouldn't work for you. (the first one does take a while as menem stated)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    10-09-2019
    Location
    Los Angeles, CA
    MS-Off Ver
    2017
    Posts
    14

    Re: Return Only Data That Appears in Three (Or More) Columns

    Hi JeteMc, thanks for the response. I think the issue might be that I'm trying to use this on Google Sheets... have been trying to figure out how to make it work but it doesn't seem to go. Any thoughts? Thanks!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Return Only Data That Appears in Three (Or More) Columns

    Ask one of the moderators to move the thread to the "For Other Platforms(Mac, Google Docs, Mobile OS etc)" forum, where contributors with Google Sheets knowledge may see it.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    10-09-2019
    Location
    Los Angeles, CA
    MS-Off Ver
    2017
    Posts
    14

    Re: Return Only Data That Appears in Three (Or More) Columns

    Unfortunately it looks like I can't contact the moderators yet because I'm still a new member on here :-(

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return Only Data That Appears in Three (Or More) Columns

    In Google Sheets, put this in H5:

    =filter($B$5:$B,countif($D$5:$D,$B$5:$B)>0,countif($F$5:$F,$B$5:$B)>0)

    and it will fill in any additional required rows.
    Rory

  9. #9
    Registered User
    Join Date
    10-09-2019
    Location
    Los Angeles, CA
    MS-Off Ver
    2017
    Posts
    14

    Re: Return Only Data That Appears in Three (Or More) Columns

    Hi Rory,

    That worked! Thank you for the help.

    Any idea on a way to have it still display the repeated shops if one of the columns is empty? For instance, if I deleted the data in column F, I would still want it to return both Abby's and Brenda's.

    Thanks!

+ 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. [SOLVED] Displaying the data matches that appears in multiple columns
    By RoundaboutCJP in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-11-2019, 05:51 PM
  2. Replies: 4
    Last Post: 06-16-2016, 12:15 AM
  3. Replies: 3
    Last Post: 07-21-2014, 01:34 PM
  4. How to see data everytime a value appears in multiple columns
    By lauriexstevens in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-28-2013, 11:58 PM
  5. If value appears in a range return 1 value, if not return another
    By whirlwind1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-21-2011, 11:23 AM
  6. Copy certain rows and columns if data appears in one cell
    By pmarsella in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-21-2009, 04:11 AM
  7. Replies: 2
    Last Post: 06-08-2005, 11:05 AM

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