1

Is it possible to prevent duplicate URLs in google sheet like the following:

User enters URL: https://superuser.com/questions/ in a row of column C. If user wants to enter any subdomain or other URLs from superuser.com in other rows of column C, a warning is shown to user. For example this URL should be flagged as duplicate: https://superuser.com/

| |
  • 1
    Not so much as prevent but flag any duplicate by using conditional formatting on the cell range column C but much would depend on the author of the question complying with your rules - which may be impossible. – User24601 Nov 17 at 17:48
  • 1
    Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Nov 17 at 18:47
0

You have a column where users enter url data. If the domain for any given url has already been entered, you want to flag that entry as a duplicate.

In this example,

  • a helper column (Column D - which can be hidden of have a small width) extracts the domain from the url in Column C.
  • a formula in Column E identifies whether the entry is a duplicate.

Helper Column - get the url

  • =arrayformula(iferror(trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(C2:C,"https?://",""),"^(w{3}\.)?","")&"/","([^/?]+)")),""))
  • enter this once in cell D2; it will copy itself down the column; if you wish, you can limit the number of rows by including array_constrain.

Unique/Duplicate

  • =IF(COUNTIF($D$2:D2,D2)>1,"Duplicate","Unique")
    • Enter this in cell E2 and copy this down as many rows as necessary.
    • This formula accepts the first instance of a domain as "Unique", and flags later instances as "Duplicate".

Conditional Formatting

  • green if value = Unique, red if value = Duplicate.

Sample

Screenshot

| improve this answer | |
  • Thanks. The Helper Column works. but Enter this in cell D2 and copy this down as many rows as necessary. Do you mean E2? The Unique checker not working – Rembo Nov 19 at 12:15
  • Yikes. You're quite right. Should be cell E2. – Tedinoz Nov 19 at 12:29
  • It is showing unique even for duplicates. should I Just copy that or some modifications needed? – Rembo Nov 19 at 12:50
  • Impossible to say without knowing what urls are generating inconsistent outcomes. One option: make the mods, and be sure to add detail in a comment so that all can benefit. Another option: edit your question to include some sample data (text based); include (and flag) the urls that are “problematic”.or do both ;) – Tedinoz Nov 19 at 23:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.