Auto generate serial number

**silambarasan.J** · 01-25-2015, 03:31 PM

Silambu.xlsx

Hi

Our company is running all over the india. Daily dispatches is depends on the branches. I have to give a serial and dates to each and every braches.

I want the serial numbers to be change according to the dates / Branch names. ( Its upto the requirements of the branches, such as on the date, there would be disptaches to different braches, (should not be

for example on 25/01/05 I choose Banglore the SL.No would be 1. (which I will be given as a default number). Then on the same date if I choose Goa, I want the serial number to generate automatically to 2 and so on.

Look forward to the answers please and Thanks.

Regards

Silambu

**skywriter** · 01-25-2015, 06:19 PM

I've looked at your data and your request and I'm having a hard time figuring out how you want the numbers generated.

If you could manually create several serial numbers and explain how you came up with those numbers it would be very helpful.

**ELeGault** · 01-26-2015, 03:43 AM

You appear to be doing some circle Indexing...meaning you are repeating steps to duplicate values to the side of that in which they are. The Match formula will index the data and find the value in the row/col (Generate) so that you do not need to do a lookup to get its location. All that aside, from what I can tell you are asking for a counter based on date...

Example

If on 1/26/2015 you choose the following sites

AHMEDABAD
AHMEDABAD-W/S(VASTRAPUR)
BANGALORE
BANGALORE-W/S

You are wanting a "Serial number" which is really a count by order of selection. Always restarting with the day, so the list would be 1,2,3,4

1 AHMEDABAD
2 AHMEDABAD-W/S(VASTRAPUR)
3 BANGALORE
4 BANGALORE-W/S

Is this the case?

If so you simply need to base this off of the date countifs -

=COUNTIFS($D$2:$D2,$D2)

You will need to add more to this process if you plan on entering the same branch in on the same day in a new line. Otherwise this will do it.

Now if this truly is the case I would say be careful of application as making something 1 today and then a different branch 1 tomorrow simply based on order of entry is a road to messy data. I will hold off on going too much further as I am getting into assumptions and I think if you clarify further that will help.

**silambarasan.J** · 01-31-2015, 08:33 AM

I have used searchable suggestion dropdown list in B2. As you can see, if I type branch name in B2 it automatically appears in C2. There shouldn't be a dispatch to all the 44 branches on the same day, its depends on the daily bases. It various and it can't be on ascending orders, such as it could be any from the list of branches.

Now my question is,

With having a key as a date (B3) and branch name (C2), I want the serial number (SL.NO - in A2) to generate automatically against branch name.

E.g.., Firstly, In A2 I will give a unique serial number (5545 for Bangalore). Then If I Choose Ahmedabad, I want the serial number to generate automatically to (5546).

Again, its depends on the daily bases branch, I will not know which Branch I will be choosing.

pl help.