Thread: find duplicate numbers in string

Hi, I have a column of numbers I am recording but some cells in the column can contain more than one number.
For example. cell A1 contains "1", A2 contains "2", A3 contains "3, 4" A4 contains "5"

If I put any of the previously used numbers (1 through 5) in cell A5, I would like a warning.

BTW. When I have to have 2 or more numbers in one cell I can use any delimiter between the numbers. The numbers in real life are not sequential as per my simplified example.

I can't get FIND or SEARCH to work in this instance as I assume I am looking for a number in a text string.

Any help would be greatly appreciated.

Thanks
Brian

Originally Posted by ozbrian

Any help would be greatly appreciated.

I've seen much harder things than this solved on the forum so I assume I'm not being very clear.

I have attached a simplistic version of the problem in an excel file.

Fingers Crossed
Brian

**bump** Would really appreciate some help on this one.

Select the coloumn you are going to enter the Data. use conditional formatting,format only unique or duplicate value, format .This will give indication when you enter a duplicate data.

Originally Posted by nadarajan

Select the coloumn you are going to enter the Data. use conditional formatting,format only unique or duplicate value, format .This will give indication when you enter a duplicate data.

Thanks for the reply! I'm sharing the spreadsheet with excel 2003 users so the conditional formatting won't work. I was hoping for a formula that will work across all versions.

Originally Posted by Azam Ali

see attatchment

Thanks Azam,

It almost works and is much better than what I have now. The issues that I've found with your formula are;

• if 100 appears before 10 in the list, 10 will flag as a duplicate.
• if 3, 6 appears in the list, a 3 OR a 6 will flag as a duplicate BUT 7, 3 will not flag as a duplicate even though 3 has been used.

Point 1 is not an issue in my real life spreadsheet but point 2 is.

Maybe this is something excel can't do?

Brian

Originally Posted by Azam Ali

Point 2 is not clear as it is working fine.

relevent with your point 2, upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

Thanks Azam,

I've attached a spreadsheet in the hope of making it clearer.

Best Regards
Brian