Excel tips: how to create unique identities

Question

I have a list of duplicated codes and I want to make them unique so that I can refer to them individually. Is there an easy way to achieve that?

Answer

The easy way to make a duplicated code unique is to add a unique number to the end of the code. The easy way to do that is to use the COUNTIF function in combination with the TEXT function to achieve the correct structure for the code.

When adding a numeric code to the end of a text code, I highly recommend using leading zeroes with the numbers. This allows you to sort the code correctly. See Figure 1 for an example of the problem of not using leading zeroes.

The codes in column A have been copied to, and sorted, in column C. You can see that all the numbers starting with 1 have been grouped together. This is not usually the order that you would expect. If you use leading zeroes, sorting will be done correctly.

The number of leading zeroes will be determined by how many unique codes you need. If you need less than 100, then two digits will suffice. The example in Figure 2 uses three digits, which allows for 999 unique codes per text code.

Column A has the duplicated text codes. They are grouped together in the example, but this technique will work no matter what their order. The formula in cell B2, which has been copied down, is:

=A2&TEXT(COUNTIF($A$2:A2,A2),“000”)

The & symbol joins text together and the ABC entry in A2 is joined to the result of the TEXT function. The COUNTIF function within the TEXT function provides a sequential number by performing a conditional count using an ever increasing range.

The COUNTIF function in the example counts how many times the code in column A of the current row appears in the range starting at $A$2 and finishing in column A with the current row. As the range expands, the number of entries that match the current row can increase.

The first part of the COUNTIF function is the range to count, the second part is what to count. The COUNTIF function will provide a unique sequential number for each of the codes as it is copied down.

The TEXT function takes the resulting sequential number from the COUNTIF function and applies the format “000”. This format fills digits on the left with zeroes. So 1 is displayed as 001, 10 is displayed as 010, and 99 is displayed as 099.

The formula in cell B10 is:

=A10&TEXT(COUNTIF($A$2:A10,A10),“000”)

You can see that the COUNTIF range has expanded from the original formula.

Once you have created the formulas, you need to capture their results so they won’t change if the list is sorted. The easy way to achieve this is to copy the whole column by clicking on column letter B and copying.

Then click the Paste drop-down in the Home ribbon tab and choose the Values icon which displays 123 (see Figure 3). You can also right click column B and use the Paste Values icon.

This removes all the formulas by pasting their results into their respective cells. The new unique codes will then be fixed and ready to use.

If you paste values frequently you may like to use the shortcut Alt h v v which, when pressed in sequence (not held down), will paste values. This shortcut works in Excel 2007 and later versions.

Sometimes keyboard shortcuts are a quicker way to apply formats than the mouse. Try these keyboard shortcuts to apply some standard formats on a selected cell or range.

You can apply the date format, d-mmm-yy, by using Ctrl + Shift + #. This is a handy format to use as it makes sure there are no misunderstandings with US date formats.

A number format, using the comma and two decimal places, can be applied by using Ctrl + Shift + !.

As you might expect Ctrl + Shift + $ applies the currency format with two decimal places, while the shortcut Ctrl + Shift + % applies the percentage format, with no decimal places. A thin outline border can be applied using Ctrl + Shift + &.

Ctrl + Shift + _ (Ctrl + Shift + underscore character) will remove borders from the selected range.

Once you have applied a format, you can use the F4 key to repeat the format on other ranges. The F4 key repeats the previous action. It doesn’t work on all actions, but it does work with quite a few.

Neale Blackwood CPA is CPA Australia’s resident Excel expert. Send your questions to [email protected].