In this tutorial we are going to look at two methods of labeling or numbering accounts, locations, inventory, etc. using IF statements and Custom Formatting to count the frequency in a list or label the items in a sequence.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
I have a list that could represent parts, inventory, accounts, customers, etc.:
I want to number this list two ways:
1) Count the number of instances of the same name in the list;
2) Number each unique instance of a name, then increase the next name by one.
Count the number of instances of the same name in the list:
To accomplish this, all I need to do is enter the following formula in the cell next to the first item in the list, in this case cell C2:
Then copy it down to the bottom of my list:
This formula will check to see if the name above is the same. If so, it will take the number above and add 1 to it, if not it will insert the number 1.
If I then want to convert these to values only, I can use this trick:
I right-click on the edge of the highlighted area, drag it off, then back on the same range, and click on “Copy Here as Values Only”.
Number each unique instance of a name, then increase the next name by one:
In this case I want to number the same items with the same number, then when the item changes, increase the number by one. To start, I need to enter into the first cell whatever starting number I want to use. Here I will enter “201”:
Then, in the cell below, I will enter this formula and copy it down:
This formula will check to see if the name above is the same. If it is, it will enter the same number as above. If not, it will add a 1 to it.
Now, let’s say that I want to add a location indicator to the beginning of the number, for example, “GR-” so that the numbers will be GR-201, GR-202, etc. To do this, I’ll create some custom formatting. I’ll highlight the cells I want to format, call up the Format Cells dialog box (I use the keyboard shortcut Ctrl + 1), click on Custom and enter into the Type box “GR-“000:
This tell Excel to put the label GR- in front of whatever number I enter, and make that number 3 digits long:
So, notice that, even though I only entered 201 in cell G2, the formatting changed it to GR-201, and formatted all the cells below in the same manner.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!