Looking to prevent duplicate text in cell

Hello everyone,

Before I explain the problem I'm looking to solve, I'd like to say thanks and I appreciate all the help I've gotten so far on this project I'm working on. This fix should be the last one to complete my project - then pester you guys less frequently.

I have one main tab which I pull data from other various tabs. To match data the data, I create a "common unique ID", in other words I create a column on each tab and connect various cells with the ampersand.

So in each of the various tabs (and the main tab), I'll have a column with something like =A2&B2&C3

If the "unique ID" matches between the tabs it will pull back the desired phrase, or it will be left blank. (Just using an =IF(ISNA) formula)

If you have followed so far, on my main tab, I have a column for each of the various tabs. The information I am trying to match could be on any of the tabs, so I need a column using an =IF(ISNA) formula to attempt to match each tab. So, on the main tab, there are several columns which might have the phrase I am looking for. I use another ampersand function to add those columns. My problem is that occaisionally, what I am looking for is on multiple tabs. So, if I am normally pulling the phrase "Pending Loans Report" when a match is made, if it is on two tabs, the cell will add it twice "Pending Loans ReportPending Loans Report"

I realize this is confusing, so I attached a spreadsheet. The "main" tab I was referring to is sheet1. I really simplified it down to post here, but I think the main idea of my problem is still evident. The problem is line 15. Note how column M has "Pending Loans ReportPending Loans Report" - I'd like it to only say this phrase once. The macro pastes special values at the end, but I've highlighted some cells in red which show the formulas I used for that column. Columns N, O, P, Q, etc. all have virtually the same formula.

If you've managed to follow all this - thanks and give me any suggestions. If you know of a better way to do what I am trying to accomplish, I am all ears.

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.

First, here's a better example of how the criteria test works...first a couple of columns of data:

      A         B         C          D
 1   red      dog1       red      dog1, cat1, dog3, bird2, cat3
 2   red      cat1
 3   blue     dog2
 4   red      dog3
 5   blue     bird1
 6   yellow   cat2
 7   red      bird2
 8   yellow   bird3
 9   red      cat3
10   blue     fish1

The formula in D1 that gives us that answer is:
=ConCatIf(A1:A10,C1,B1:B10,", ",TRUE)

First parameter: Range to evaluate
Second param: comparison string. (I chose an a cell with a color in it)
Third param: Range to return values from
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

I compared the value in C1 to ALL the values in column A, then I string concatenated the values in B that match the rows with the matching criteria in column A.

I think this is clearer with this more common implementation. Once you can use this UDF this way, you can adapt it work in only ONE column.

First, I replace the second range (columnB) with a duplicate set of the first range (columnA). Then, just like I would do to test values in a SUMIF() formula, I change the third parameter (C1) to a value test on a cell I know has nothing in it and use syntax that says "every value that DOESN'T match that cell"...or "greater than or less than this cell".

In a SUMIF(), you do that with "<>"&BB1 (assuming BB1) is empty.

So, now this formula:
=ConCatIf(A1:A10,"<>"&BB1,A1:A10,", ",TRUE)
...reads as "for every cell in A1:A10 that IS NOT blank, string concatenate the values together with a comma/space separating them.

Is that enough?