+ Reply to Thread
Results 1 to 7 of 7

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
    12

    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
    Registered User
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    148

    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.
    My English is very poor, so please be patient >_<"

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

    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 2010/2019
    Posts
    7,906

    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
    12

    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 2010/2019
    Posts
    7,906

    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
    12

    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 :-(

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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