+ Reply to Thread
Results 1 to 27 of 27

[GOOGLE SHEETS] Formula to compare Domain names and associated status between workbooks?

  1. #1
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbooks?

    Hello all,

    I'm a newbie on this forum, so please bear with me while I learn!

    I need help in identifying the right formula to use to compare different workbooks; One workbook (an inventory) where people give a status for a specific URL (e.g. Not Started, under review or Complete).

    In another workbook which, a register of all URL, I need a formula (vlookup?) to check the previous workbook (the inventory) and give an indication of it's status (not started, under review or complete).

    Now the tricky part is that people enter the domain name but with different URL on a workbook while I may have a different URL in another workbook (Sorry I'm new I cannot provide URL examples).

    Not sure if that makes sense,

    Thanks in advance for your help!

    Sartoris
    Last edited by AliGW; 11-03-2021 at 05:54 AM. Reason: Moved to correct forum section.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,831

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook as it is not possible to give a reply without knowledge of the data..

    domain name but with different URL on a workbook while I may have a different URL in another workbook
    Please ensure examples of the above are included.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,162

    Re: Which formula for comparing Domain names and associated status between workbooks?

    can you attach a small sample spreadsheet with examples of what has been entered and the type of issues - see the yellow banner
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Thanks, I attached two workbooks examples.

    Best,

    Sartoris
    Attached Files Attached Files

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,162

    Re: Which formula for comparing Domain names and associated status between workbooks?

    so the URL looks the same , maybe some more explanation of what we are doing and sheets to use

    my guess
    you want the status from column J - Website Inventory sheet , Inventory Workbook

    In the Sheet All Sites - 210921 - registry example
    you want column B populated

    if so
    =VLOOKUP(C2,'[Inventory example.xlsx]Website Inventory'!$B$2:$J$6,9,FALSE)

    should work
    I thought the URL where sometimes entered wrong ??
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Your assumption is correct _ I wanted column B populated. I'm sorry if I haven't made myself clear.

    I will try, but my assumption was that the VLOOKUP formula will not identify the URL if it was not an exact match. It looks like my assumption was incorrect.

    Thanks!

    Sartoris

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,162

    Re: Which formula for comparing Domain names and associated status between workbooks?

    But they are an Exact match in your example
    my previous post
    examples of what has been entered and the type of issues
    so we can see the type of miss match

  8. #8
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    I made something silly! You're right (duh!) the URL's were wrong, well spotted!

    I re-attached the workbook but URL mismatched this time. Sorry...
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    994

    Re: Which formula for comparing Domain names and associated status between workbooks?

    You may simplify URL's with this formula.

    =SUBSTITUTE(MID(A1,IFERROR(FIND("//",A1)+2,1),LEN(A1)),"www.","")

    It will remove any string before // and also www. from URL's.

    Regards.

  10. #10
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Hey menem,

    Thanks for the response, I will give it a try today and let you know more if that works.

    Best,

    Sartoris

  11. #11
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Hello,

    Sorry for the delayed reply.

    I tried menem suggestion to simplify the URL but it looks like I cannot enter the formula; I encounter an error that I can't rectify.

    I attached my document, I'd be grateful if anyone coule help me to rectify it.

    Thanks in advance,

    Sartoris
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,831

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Copy/Paste the formula

    =SUBSTITUTE(MID(C2,IFERROR(FIND("//",C2)+2,1),LEN(C2)),"www.","")
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  13. #13
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Hello,

    Thanks John but for some reason I have an error message saying that this formula is incorrect, see attachment.Spreadsheet_Registry_Examples_FORMULA_Error_Message_03112021.png

    Thank you in advance,

    Sartoris

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Which formula for comparing Domain names and associated status between workbooks?

    You have a European locale, so need semi-colons instead of commas.

    Try this (German):

    =WECHSELN(TEIL(C2;WENNFEHLER(FINDEN("//";C2)+2;1);LÄNGE(C2));"www.";"")

    or this (French):

    =SUBSTITUE(STXT(C2;SIERREUR(TROUVE("//";C2)+2;1);NBCAR(C2));"www.";"")

    https://en.excel-translator.de/translator/
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  15. #15
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Merci AliGW and others in this thread

    Part 1 solved, now I'm onto part 2 (matching statuses)!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Which formula for comparing Domain names and associated status between workbooks?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED, and then start a new thread with a suitable title for part 2.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  17. #17
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Sure, I did.

    However I'm using a spreadsheet on Google Sheets where the spreadsheet settings are set to Locale UK, and English language.

    As you can see below, I still have an error message appearing:

    Attachment 754077

    Thanks,

    Sartoris

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Which formula for comparing Domain names and associated status between workbooks?

    OK, well you've posted in the wrong forum section, then!!!

    This is something you should have stated right at the outset. I am moving the thread to the appropriate section.

  19. #19
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Which formula for comparing Domain names and associated status between workbooks?

    Thanks and sorry for that, it wasn't intentional.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    When you start the new thread, please create a title that says Google Sheets and post in the section this is now in (Other Platforms). Thanks.

  21. #21
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    Sure, will do so.

    Please find a screenshot with the actual error message Attachment 754080

    Thanks,

    Sartoris

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    You have used single quote marks (' ... ') around WWW. - try "WWW." instead. Sheets seems to be trying to read it as a named range.

  23. #23
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    Thanks AliGW, it actually didn't have any quote marks, I added double quote marks, and it worked.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    I know - it did make me giggle, since John's original formula and my two translations all contained the double quote marks. Bizarre that you decided to leave them out, really, but there we are - you live and learn.

    Now, I think it's time you marked this as solved and started a new thread on the second issue in the right place with all the right information, please. Thanks.

  25. #25
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    to simplify the checking, you should first import the data into the sheet that will do the checking.

    here is the "HowTo" guide at google support:
    https://support.google.com/a/users/answer/9308940

    after the data is imported to a sheet, then you can do all checking the imported data.

  26. #26
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    Quote Originally Posted by AliGW View Post
    I know - it did make me giggle, since John's original formula and my two translations all contained the double quote marks. Bizarre that you decided to leave them out, really, but there we are - you live and learn.
    I will learn to pay attention for sure
    It wasn't intentional, I copied it between my personal PC into Outlook to send it into my work PC, and somehow they got lost in the process!

    Quote Originally Posted by AliGW View Post
    INow, I think it's time you marked this as solved and started a new thread on the second issue in the right place with all the right information, please. Thanks.
    Sure, will do, thanks again!
    Last edited by AliGW; 11-03-2021 at 06:26 AM. Reason: QUOTE tags fixed.

  27. #27
    Registered User
    Join Date
    09-21-2021
    Location
    Basel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: [GOOGLE SHEETS] Formula to compare Domain names and associated status between workbook

    @janmorris

    Thanks janmorris, I will try this.
    Last edited by AliGW; 11-03-2021 at 06:28 AM. Reason: PLEASE don't quote unnecessarily!

+ 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] stripping out only the domain names
    By TheVolkinator in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-18-2016, 07:31 PM
  2. Need a macro to compare workbooks and highlight names according to criteria
    By Sticker26 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2014, 03:22 PM
  3. Formula required to compare names on two different work sheets
    By Spaz1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 05:59 PM
  4. Compare Two sheets in different workbooks
    By kmlprtsngh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2010, 12:44 AM
  5. VBA to compare 2 sheets/workbooks
    By bapatterson in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-12-2010, 01:04 PM
  6. Compare names across different workbooks
    By neodjandre in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2007, 06:19 PM
  7. Tracking domain names
    By \(Joseph in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2006, 04:25 AM

Tags for this Thread

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