Unique Values with Hyperlink Carry Over

[tbdbitl08]

Hi there!! I am working on finding an Excel solution, if one exists, that recreates some functionality within Google Sheets that we have been using and want to switch entirely into Microsoft.

I have a somewhat large list of values with many duplicates, and I want to pull out all of the unique values for those columns. However, each of those values is a a formatted name with a hyperlink to a specific file associated with the name in the raw dataset, which is an export from a program.

The Dataset looks like below - with the first column exported by the program having the client's name with a hyperlink directly to their file, a second column with a note, and a third column with a link directly to the note itself which is also a hyperlink).

Clients (Hyperlink)	Note	Note Link (Hyperlink)
Name of Client 1	Note on Client 1	Link to Note on Client 1
Name of Client 2	Note on Client 2	Link to Note on Client 2
Name of Client 3	Note on Client 3	Link to Note on Client 3
Name of Client 1	Note on Client 1	Link to Note on Client 1
Name of Client 1	Note on Client 1	Link to Note on Client 1

When I run the Unique function, it does a great job of returning the unique values (like below) however it does not "pull" the hyperlink from the dataset over.

Clients (NO Hyperlink)	Note	Note Link (NO Hyperlink)
Name of Client 1	Note on Client 1	Link to Note on Client 1
Name of Client 2	Note on Client 2	Link to Note on Client 2
Name of Client 3	Note on Client 3	Link to Note on Client 3

Th system was originally built in Google Sheets (which does pulls over the hyperlink associated of the unique value when using the unique function) however this functionality appears to not work in Excel. I had even tried rebuilding from scratch, not just downloading and opening up the sheet in Excel, to ensure it was in Excel and not something with the transfer.

Am I SOL on this functionality, or is there something I may be missing?

Thanks for the assistance!!

[#2]

@tbdbitl08 

Instead of using the UNIQUE function, please select the data and use Data>Remove Duplicates.

I believe this will retain the hyperlink. 

[#3]

I appreciate the suggestion, but unfortunately for what this is supposed to do I don't think that will be a viable option. The end goal of what this accomplishes is that it selects all of the correspondence with a client, and each correspondence is recorded as an individual row in that dataset. The sheet that has the unique function has several other functions on it, but what it mainly does is finds the most recent correspondence with the client and pulls some metrics and conditional formatting based on company criteria.

For example we have the data set with 10000 rows, but only have 200 clients. The unique function pulls out the 200 clients, and the other formulas populates the columns in the row with the information pertaining to the most recent contact we've had with the client.

I hope that makes sense, but also illustrates why I think the remove duplicates feature may not work.

Thanks!

[#4]

@tbdbitl08 

I believe you're going to need a VB function to pull those hyperlinks.  There may be a UDF you can nest with UNIQUE that's array friendly.

@HansVogelaar or @OliverScheurich may be able to help.

[#5]

Thank you for the response! I was afraid that might be the case and likely out of my wheelhouse. Definitely a bummer that it works in something basic like Sheets but not in the superior tool, Excel. Maybe someone from Product will see this and decide to implement it some day!