+ Reply to Thread
Results 1 to 4 of 4

Query in Google Sheets

  1. #1
    Registered User
    Join Date
    03-23-2021
    Location
    Hamburg, Germany
    MS-Off Ver
    Office 365
    Posts
    2

    Query in Google Sheets

    Hello everyone,

    I would like to ask for some help with the following issue.

    I have a list of names in a google sheet and would like to find a way to check if a certain name is already in this list without revealing the entire content of it. Similar to when I sign up to a forum, choose a user name and immediately get the feedback if the name is already in use or not without being given all the user names that are signed up. Is there a way to make this work with google sheets? If not is there another (inexpensive) way to do this?

    Many thanks in advance!
    Philipp

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Query in Google Sheets

    Not exactly sure I understand. Maybe try =COUNTIFS(list of names,name) will return 0 if the name is not in the list of names or something greater than 0 if the name is there.
    Or =IFERROR(MATCH(name,list of names,0),"name not present") will return a number (corresponding to the position of name in the range vector) if the name exists or "name not present" if it does not.
    Or some other variation based on a count if function or lookup function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-23-2021
    Location
    Hamburg, Germany
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Query in Google Sheets

    Sorry, I didn't explain it well.

    I have two seperate files X and Y that each contain a list. I want to be able to check if the names in list Y already appear in list X with one caveat: the person who manages list Y is not supposed to be able to gain access to the entire list X. He should just be able to check if the names in "his list" Y already appear in X. I've tried to work with a combination of query + importrange and countifs + importrange. However the problem seems to be that as soon as you connect two google sheets you open up the entire file so someone can use IMPORTRANGE to import the entire list X and from there copy it into an external list.

    Hope this is a little clearer.

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

    Re: Query in Google Sheets

    Mr. Shorty's COUNTIFS suggestion should still work, or at least it should in Excel.
    The caveat being the the list of names argument would need to contain the full file path for the file containing list X since presumably the workbook containing that list would be closed.
    So say that list X is in cells A2:A4 on Sheet1 of Book1000 on the D drive in the Documents directory and contains Name1, Name2 and Name3
    List Y is in cells A2:A3 and contains Name1 and Name4 and that cell B2 contains the formula: =COUNTIFS('D:\Documents\[Book1000.xlsx]Sheet1'!A$2:A$4,A2)
    The formula is dragged down to cell B3
    Cell B2 will display 1 because Name1 also exists in list X and cell B3 will display zero because Name4 doesn't exist is list X.
    Note that if COUNTIFS doesn't produce expected results you might also try: =SUMPRODUCT(--('D:\Documents\[Book1000.xlsx]Sheet1'!A$2:A$4=A2))
    I don't know much about google sheets, so this is probably the best I can do.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Google Sheets query function replacement
    By Ollie Frith in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2019, 05:41 PM
  2. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  3. [SOLVED] Converting Google Sheets QUERY to Correct Excel Formula
    By davis909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2017, 09:14 AM
  4. Google Sheets QUERY How can I do the same in Excel?
    By Ziggy21 in forum Excel General
    Replies: 12
    Last Post: 10-04-2015, 07:54 PM
  5. [SOLVED] Converting Google Sheets document with query to Excel
    By playmate in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-04-2015, 10:18 PM
  6. Google Sheets Query IF column problem Converted to Excel Function?
    By Garthilk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-24-2015, 10:37 PM
  7. query w/ minimum requirement in google sheets
    By alansoftpublisher in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 12-14-2014, 04:59 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