Generate a unique reference number?

[leondixon]

Hi,

I'm wanting some sort of formula to generate a reference number that relates to numbers i put in a cell. i want the end result to look something like this:

Reference number
362 3620001
456 4560001
362 3620002
487 4870001
456 4540002

As you can see id like it to generate a reference number to the number that i input, and also when i input that number twice or three times it picks that up on the reference number also.

Is there a possible formula for this? Any help would be brilliant!

[benishiryo]

welcome to the forum, leondixon. i assume your last one should be 4560002? if that's the case & data starts in A2, try:
=A2&TEXT(COUNTIF(A$2:A2,A2),"0000")

that's a text by the way. if you need it recognized as as number, add 2 negatives in front to convert it
=--(A2&TEXT(COUNTIF(A$2:A2,A2),"0000"))

[leondixon]

Hi,

Yes that works great, thank you!

Just another question, it may be quite simple but at this time in moment my mind has gone blank! How do i apply that formula to a whole cell, so i dont have to keep dragging the formula down to carry it on?

[leondixon]

Hi,

Yes that works great, thank you!

Just another question, it may be quite simple but at this time in moment my mind has gone blank! How do i apply that formula to a whole cell, so i dont have to keep dragging the formula down to carry it on?

[benishiryo]

you're very welcome.

you mean the whole column? that's not advisable. an xlsx file means you would have 1 million rows of formulas. if your formula is in B2 & you want to copy the formula until where your data stops,
1. click on B2
2. hover the cursor until it is at the bottom right of the selected cell. you should see a black cross. do a left mouse double-click

if you want to prepare some additional rows of formulas but don't want "0000" to show up when it's a blank, try:
=IF(A2="","",A2&TEXT(COUNTIF(A$2:A2,A2),"0000"))

please mark the thread as Solved if opening question is answered. thanks
=)