Looking to prevent duplicate text in cell

The use of A1&B1&C1&D1....etc is called String Concatenation and it does cause headaches, doesn't it? I have a collection of User Defined Functions for string concatenation. One of them has the ability to spot duplicates and suppress them.

Unfortunately, it's hard to test on your sheet, so here's a simple explanation and sample sheet. First, here's the code for it, and it goes in a standard module:

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

It's used just like a SUMIF() formula, but it has two extra parameters.

=ConCatIf(A1:A10,"<>"&BB1,A1:A10,", ",TRUE)

First parameter: Range to evaluate
Second param: comparison string. (I chose an empty cell, so this selects all cells that aren't empty in the range)
Third param: Range to return values from (I used the same range as first param, but you don't have to)
Fourth param: The delimiting string for the concatenation, I'm using a comma/space in this example
Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values

This should give you enough info to apply this UDF to your sheet and use it place of that formula of your own...your sample formula of:

=N23&O23&P23&Q23&R23&S23&T23&U23&V23&W23

...would become:

=ConcatIf(N23:W23,"<>"&BB23,N23:W23," ",TRUE)

I'm very close. I know that I am very close. The VBA is way beyond my level of understanding - but I should be able to simply paste that into the bottom of my module right?

I've taken a look at your sample and I understand it - but when I try to import it into my example, every cell reads "#NAME?" - and of course I've altered the formula to fit my spreadsheet.

I know I am very close. For instance, Line 2 is the first line I enter formulas. Starting in column 'N' is my string concatenation. Columns O:X contain the values, which potentially duplicate (but I only look for values to match on a row by row basis). So normally column N would have been =O2&P2&Q2&R2&S2&T2...and so forth to X

I currently have this pasted through column N: =ConcatIf(O2:X2,"<>"&BB2,O2:X2," ",TRUE)

Also, if you don't mind can you explain the second parameter a little more. The whole "<>"&BB2 part. I'm trying to learn and not just copy. I am very grateful for the help and thank you for providing me with the information you have so far, and you probably don't remember me because I'm guessing you help a lot of people, but you have helped me a few times in the past. Thank you for sharing your knowledge.