How do I make S/N Serial numbers in excel increment based only on entries in 1 column?

Question

For example, I am creating a business excel street to record sales of my product.

Every entry will have:
-Name
-Design of product

Customers will be allowed to purchase more than 1 product, but I want to log it in such that the 2nd product onwards will occupy the cell directly below the previous. Therefore, the cell under the Name column of the same customer will be empty if he chooses to buy multiple items. 

I need this so that I can count the number of cells at the end to record the total number of sales.

However, my dilemma comes when I want to automatically assign the S/N to each customer. 
So my question is, how do I set the S/N to automatically increment based on only entries in 1 column?

swdarklighter · Answer

It is easy to increment something like a serial number in Excel. In your S/N column, start by actually typing in the first two serial numbers you want. For example, if your first two serial numbers are 1A1234 and 1A1235, type those numbers into cells B2 and B3. Highlight both cells and then click on the little black box in the lower right hand corner of your highlighted selection. Drag this down the length of your list. This will give you sequential serial numbers. If you don't need to use letters, that will work. However, this doesn't always work if there will be letters at the end of the number. If you need letters there, you will probably want to create a custom text format in Format Cells to add a repeated letter sequence at the end of your number.

I would recommend entering the name of the customer multiple times, once for each item purchased instead of leaving the rows blank. This is quite easy to do. When entering the second item, just start typing the very first few letters of their name. Excel will find that name from the list and you can "autofill" that cell. 

The reason I recommend putting their name in every time is that if you do a sort, say by item type instead of by customer name, you can still see who ordered that item. If there is no name there, you won't know exactly who that item was sold to. Also, if you happened to sell your items out of order you wouldn't be able to tell that item belonged to.

? · Answer

Just base it on a running count of the names:

Column D: Names
C2: =COUNTA($D$2:D2)

Every time a new name is seen, the count will increase.

You can extend this formula to extend from a starting number, to add prefixes and/or suffixes, and to skip blanks:
C2: =IF(ISBLANK(D2), "", "CUST" & (COUNTA($D$2:D2) + 34))
C2: =IF(ISTEXT(D2), "CUST" & (COUNTA($D$2:D2) + 34), "")

As you drag this down column C, the COUNTA range will expand.

Getting a count at the end is as simple as using a formula that counts the entire column range (D:D), or by using an Excel Table (Ctrl+T) and including a Totals row.

Trending News

How do I make S/N Serial numbers in excel increment based only on entries in 1 column?

2 Answers