Here is volume 2 of my Excel Super Links. I really did not think that I would still be posting these links to issue #60, but a lot of people seem to like these posts, so I will continue. Enjoy this collection (150 links).
You can download the collection here.
Excel Super Links 31-60
In the following article:
I demonstrated a method of obtaining unique items from a 3D range. At the time that this article was written, I was unaware that the TEXTJOIN function accepted native 3D references. See:
The techniques needed to convert a delimited string to a unique delimited string have already been discussed.
So, with the starting point of a delimited string obtained by using 3D reference with TEXTJOIN, the link above will then allow you to create the unique array or delimited string.
To be clear, first TEXTJOIN is used with a 3D range argument to make a delimited string. Then, that string is used in the formula construction described in the link above to make an array of unique items, which can be subsequently used to make a unique delimited string.
Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.
If you look at the documentation of the TEXTJOIN function found at the following link,
it shows an example at the bottom of the article where the 1st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:
=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).
As you can see in the figure, each record appears to be on a single line in the cell (F9).
This same technique can be enhanced further in this way.
=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula
With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.
The example file can be downloaded here.