Automated Data Generation in Excel - Auto-Incrementing Custom IDs (Mixed alpha-numeric)

I have either an Excel question or a database question. The reason why it might be either is because, even if the data is entered into an Excel or cvs, it will be imported into a database.

Therefore, it may be more efficient to directly create the records in the database.

A quick overview of the use case. Our company has picked up 1200 store locations. We are installing equipment in monthly waves of 100.

Once we install a store, they pay a Install Fee.

Also, once we install a store, they will begin paying a license fee for that month, and every month thereafter.

Each store is going to be assigned a Store ID, and every Install Fee and every License payment the store makes will be recorded, as well as the date.

Therefore, for the year, we should have 1200 Store IDs.

Each Store ID will have one Install Fee record, so there will be 1200 Install Fee records total.

For License Payments, there will be 100 stores who will make 12 License payments (the 100 installed in January), 100 stores will make 11 License payments (the 100 installed in February), etc…. It comes out to 7800 License Payment records.

So, in total with the Install Payments, there are 9000 records.

Store ID script I would like to create a script to create Store IDs automatically, according to the following format: The first letter of the ID is “J” and then a four-digit number, starting with 0001 and working up. So the first Store ID would be J0001, the second J0002, etc.

Install Payments Script The second script I’d like to write is to add the 1200 Install Payments records automatically, one Install Payment for each Store ID.

License Payments Script The third script is for creating License Payment records for each License Payment. For all Store IDs J0001 to J0100, there would be 12 License Payment records for each Store ID because they were installed in January. For all Store IDs J0101 to J0200, there would be 11 License Payment records for each store ID, because they were installed in February.

The finished table or Excel columns would look like this:

table/Excel sheet columns:

Store ID | Description | Date | Amount
J0001 | Install | 01/31/15 | 1000.00
J0001 | License | 01/31/15 | 250.00
J0002 | Install | 01/31/15 | 1000.00