If the hyperlinked labels are unique but the URLs are different (see the screenshot # 1 below), the Unique function won’t remove duplicate hyperlinks. Then how to Unique duplicate hyperlinks in Google Sheets? In this tutorial, you can find the answer to this.
If the labels and the URLs of the two hyperlinks are the same, they are of course duplicates. I hope you have no doubt about that.
That means, the Unique function in Sheets can remove one duplicate from these two hyperlinks. But our case is different, I mean the labels are the same but the URLs are not. So we must ignore the URLs and only consider the labels in the Unique. How?
First, see some of the use of Hyperlinks in Docs Sheets below. Then we can learn about how to UNIQUE duplicate Hyperlinks in Google Sheets.
Introduction to Hyperlinks in Google Sheets
Hyperlinks in Google Sheets is versatile. Do you know, why?
It has the following purpose in Sheets. Use hyperlinks in Docs Sheets if you want to;
- Jump to any cell in the current Google Sheets file no matter which tab.
- Link to a separate Google Sheets file from the current file.
- Link to any valid URLs. For example, you can link to a wiki page, your favorite tutorial on this page, etc.
We can insert links (or say hyperlink) in two ways in Google Sheets. I have detailed that already.
- How to Label URL in Google Sheets Using HYPERLINK Function.
- Table of Contents – Yup! It’s Possible in Google Sheets – Link to Different Tabs.
Let me bring your attention back to our topic.
How to UNIQUE Duplicate Hyperlinks in Google Sheets
I have tried to clear the purpose of this tutorial in the main title itself. What we are going to do is removing duplicate values even if the hyperlinked URLs are different.
Example with One Column Data (Key Function is Unique)
You can see different URLs but the same labels/values present in column A. The UNIQUE formula in cell C2 has no effect on such values.
Screenshot # 1:
See the UNIQUE formula in cell C2. It returns all the values as it is even if there are duplicates in column A! It’s because the linked URLs are different. Only the labels are the same for the first two values.
How to ignore hyperlinked URLs and only consider the labels in Unique?
In the above example, the unique labels are “Info Inspired” and “Wikipedia”. Here is the formula to UNIQUE duplicate hyperlinks in Google Sheets.
=unique(query(A2:A4))The Query function removes the linked URLs and that helps the Unique function to remove the duplicates.
Screenshot # 2:
Tip:
To quickly remove hyperlinks using a formula in Google Sheets, simply wrap the range with Query.
Example with Two Columns Data (Key Function is SORTN)
Here is one more example. This time there are values in column B too. I only want the unique effect in column A but want a two-column output as per Screenshot # 3 below.
In this case, you should use SORTN + Query instead of Unique + Query.
=sortn(query(A2:B4),9^9,2,1,1)Screenshot # 3:
Here, similar to the earlier example, the Query function removes the linked URLs. Since there are two columns, we can’t use Unique only in the first column and retain the second.
The alternative function here is SORTN. Learn the TIE MODE 2 (Unique equivalent) argument use within SORTN to understand the above formula.
Must Read: SORTN Tie Modes in Google Sheets – The Four Tiebreakers.
You can use either UNIQUE or SORTN as above with Query to remove duplicate hyperlinks in Google Sheets when the labels are the same but the URLs are unique/different.
Related Reading:
