Need Help in Numbering a Series..which is both Continous/Non-Continuous..

**e4excel** · 10-14-2011, 05:46 AM

Need Help in Numbering a Series..which is both Continous/Non-Continuous..

Dear Forum,

I need to have 2 Serial Nos :
First Serial No will be store Unique Sr. No for the corresponding values.
Second Serial No will be to store the same Unique Sr. No everywhere the same Values occur.

I have used my own formula but need advice whether this would be the right one or not as I have used an Index Function whose Range keeps on expanding with every Row on dragging it down, though it gives the desired result, I am still not sure whether this would be the right approach to get the Sub Serial No.?

Warm Regards
e4excel

**DonkeyOte** · 10-15-2011, 03:25 AM

If your expected results are correct then your approaches are ok... personally I'd be inclined to avoid multitude of COUNTIFs (with ever expanding ranges) and might opt to use something like:

Please Login or Register  to view this content.

the initial test in B2 isn't required for the function to work, it is there simply to lessen the number of times the resulting LOOKUP calc is performed - ie wherever the current number = prior number just use the prior result.

To be honest I doubt you'd notice any difference between the above method for Column B and your existing method... unless the range were to become truly vast.

**e4excel** · 10-15-2011, 10:50 AM

Dear DonkeyOte,

I have always been a great fan of your Help and would be definitely using your approach as I was not sure whether my approach was really good though its working ...

Can you please explain me the reason of having the below Line please

Please Login or Register  to view this content.

Actually, it will be better if you explain the complete formula..

Please Login or Register  to view this content.

The First Part is the Lookup Value and the second part is the Lookup Vector which is derived from the CHOOSE Function.. but I did not quite understand the {1,2} ..

Thanks in advance

Warm Regards
e4excel

**e4excel** · 10-15-2011, 11:05 AM

Dear DonkeyOte,

On the same grounds if I had to Add a Serial Numbering System which has a Series of 1,2,3 for the same Plot nos ..

Like have series which shows the Series for each Plot no, I have added one more column in the Existing Sheet to also contain the actual Sr. no for each Plot no.

Warm Regards
e4excel

**DonkeyOte** · 10-17-2011, 08:27 AM

Originally Posted by e4excel

Like have series which shows the Series for each Plot no, I have added one more column in the Existing Sheet to also contain the actual Sr. no for each Plot no.

I don't really understand what you're asking for here, expected results in the sample generally help clarify (possibly just COUNTIF?)

The use of {1,2} within the CHOOSE populates the lookup_vector with both results (i.e. "chooses" both).

The outer LOOKUP construct returns the last number listed within the lookup_vector.

In this example it follows that should the INDEX/MATCH fail then the MAX value is used else the INDEX/MATCH result supercedes the MAX.
(it can be seen in effect to be an alternative form of error handling - given use of .xlsx you could use an IFERROR)