Solved Search for duplicates in range of textstrings

Microsoft corporation Office 2010 profes...
January 4, 2015 at 12:28:26
Specs: Windows 7
Hello all

Is there anyway to search a range of cells (like a whole column) for duplicates of text strings in another range of cells?

And example:
In Sheet2 column A I have some text strings like this one, one in each row: "Text to: 1200, 1300, 1600"

In sheet1 column A I have the corresponding numbers like: 1200, 1300, 1400, 1500, 1600 and so.

How can I check if those numbers in sheet1 column A only is used one time in those text strings in sheet2 column A? Like if I by mistake have put in the number 1200 in two or more rows with text strings in sheet2 column A.

I have tried to play around with the find, search, index_match function and so on, but with no luck.

See More: Search for duplicates in range of textstrings

Report •

January 4, 2015 at 15:12:48
✔ Best Answer
What about COUNTIF with wildcards?

Try something like this, which is based on the data set shown below:

=IF(OR((COUNTIF($A$1:$A$4,"*"&B1&",*")+COUNTIF($A$1:$A$4,"*, "&B1))>1,
        COUNTIF($A$1:$A$4,"*, "&B1)>1,COUNTIF($A$1:$A$4,"*"&B1&",*")>1,

             A            B          C
1    1200, 1300, 1400    1200    Duplicate!
2                1500    1300	
3         1200, 13000    1400    Duplicate!
4    1800, 1400, 1900    1500	

The formula counts each occurrence of the values in Column B across the range of text strings A1:A4. If the count is greater than 1, we have a duplicate (or more).

In addition to the issue mentioned in your previous thread where we have to deal with e.g. 1300 vs 13000, we also have to deal with the fact that the values could be in the middle of the string as well as at the end. Therefore we need to look at values that are followed by a comma as well as those that aren't. If e.g. 1300 vs 13000 wasn't an issue, the formula would be a simple COUNTIF:

=IF(COUNTIF($A$1:$A$4,"*"&B1&"*")>1, "Duplicate","")

If you need a detailed explanation of the reason the formula adds COUNTIF's, let me know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 4, 2015 at 20:37:40
That does the trick! Thank you very much.

As I can see now, I was close with my own formula, but I failed with "*"&B1&"*" part. There I tried TEXT(B1;), but that didnt work. Maybe I have misunderstood the outcome of that formula.

Report •

January 5, 2015 at 02:17:43
I am now trying to incorporate this into my worksheet, but have run into problems.

As we talked about in the other thread I have started. I need to concanate a Space to the last number to search for. That you explained to me to use the &" " to simulate that there is a Space at the end of the string I am searching. But now I am dealing with a range and if I understood correct, I need to attach this &" " to the end of that range. How do I do that?

Ive tried this, but it does not Work. It also returns TRUE if its 13000 and not 1300.

=IF(OR((COUNTIF($A$1:$A$4,"*"&B1&",*")+COUNTIF($A$1:$A$4,"*, "&B1))>1,
        COUNTIF($A$1:$A$4,"*, "&B1)>1,COUNTIF($A$1:$A$4,"*"&B1&",*")>1,
        COUNTIF($A$1:$A$4,"* "&B1"*")>1),"Duplicate!","")

As you can see I have altered the last CLAUSE with a "* "& and a &"*", also I have put in a >1 to check if there are more than one entry of that.

Report •

Related Solutions

January 5, 2015 at 12:25:53
First off, there is an error in the formula you posted. It can't work as is because it is missing an &

=IF(OR((COUNTIF($A$1:$A$4,"*"&B1&",*")+COUNTIF($A$1:$A$4,"*, "&B1))>1,
        COUNTIF($A$1:$A$4,"*, "&B1)>1,COUNTIF($A$1:$A$4,"*"&B1&",*")>1,
        COUNTIF($A$1:$A$4,"* "&B1"*")>1),"Duplicate!","")

I hope that arrow ended up where I wanted it. You need an & in that location.

However, even with that & (just to eliminate the error) I don't know why you feel the need to check for a space.

My formula does not return "Duplicate!" for 1300 & 13000.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 5, 2015 at 22:39:38
I have tried to take your code exactly as it is and its true it doesnt return TRUE if 1300 and 13000 are present, but my problem is, that is I have these two text strings in the range it doesnt return TRUE:

Text to: 1300, 1500, 1600
Text to: 1300

If I put a "," after the last string like this: Text to: 1300, it returns Duplicate!.

I have tried to take the last clause:


But that I can't get to Count anything?? Can you explain when this clause will be active?

Report •

January 7, 2015 at 09:54:40
Okay, after playing around with this I think I found the solution. Here is my modified version of your code:

COUNTIF($A$2:$A$15;"*"&B2&",*")+COUNTIF($A$2:$A$15;"*, "&B2))>1;COUNTIF($A$2:$A$15;"*, "&B2)>1;
COUNTIF($A$2:$A$15;"*"&B2&",*")>1;(COUNTIF($A$2:$A$15;"*"&B2&",*")+COUNTIF($A$2:$A$15;"* "&B2))>1;        COUNTIF($A$2:$A$15;B2));"Duplicate!";"")

message edited by JacobJ

Report •

January 8, 2015 at 03:49:56
I'm glad you found a solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 8, 2015 at 10:49:59
It was you who found the solution, I just modified it slightly to check for an additional thing.

Thanks for you help on this.

Report •

Ask Question