Unique ID Sequential Ref No

Hi, I need to create unique ID reference numbers if duplicates are found. Please see attached pic:

urn.png

Could I add a formula to compare Column D (generated Ref No) with Column E (All existing Ref Nos) to output a unique Ref no in Column F?

A B C D E F
1 REF INTIALS FIRST NAMES SURNAME GENERATED REF NO ALL REF NOS UNIQUE REF NO
2 AW ADAM WILLIAMS AW-0000 AW-0001 AW-0007
3 AW-0002
4 AW-0003
5 AW-0004
6 AW-0005
7 AW-0006

The last for digits are numeric and I would like the numbers to increase sequentially.

I'm a novice and would really appreciated the help.

Thx

Thanks for the quick response. I'll give it a go and update the results here.

Originally Posted by mehmetcik

Enter this formula using Ctrl Shift and Enter

Formula:

Please Login or Register  to view this content.

Hi, I tried your suggestion with a slight variation (as I've changed ref no format) and it appears to have worked. I've got one more issue, as I need it to search an existing list of URN's on worksheet URN with newly generated ones. So far it is producing the correct results. However if there are duplicates on the new list, then they are not generating as unique URN's.

I've attached a test spreadsheet for you to have a look please http://www.excelforum.com/attachment...1&d=1484418273.

I really appreciate all the help and value your time and effort.

Thx

Originally Posted by JeteMc

Try this array entered formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks you for your help. I'll give it a go and update the results here.

Thx

Originally Posted by JeteMc

Try this array entered formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

That worked a treat, fabulous. Thanks very much JeteMC for your superb help, and also to all who contributed to this thread. Keep up the good work.

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

You too. Yes will mark thread as solved. Thx again.

I'm sorry I have just realised, I missed out adding text to the beginning of the generated URN i.e. There should be a set text auto added to the beginning of the generated URN as KSB. For example AH0002 is generated from the first name and surname and before that it should add 'KSB', such as KSBAH0002. So sorry to unresolve this thread. I really hope you can further help? Thx