Automatic Sequential Number with Condition

[GemBox]

Hello All,
I have a requirement to find the next available number in the same column and to fill up automatically. For example in the same column if CR-FI-1 & CR-FI-2 exist, the system should propose the next number as CR-FI-3. Now the issue is in Column C, we have already data which we don't want to disturb. I want the system to check the column 'D' and propose from the row 101 onwards.

Sample file attached.

Thanks in advance,
Regards,
Gem

[JohnTopley]

So what value to expect in 101 given that the sequence (to date) depends on the count of Codes in Column E?

[GemBox]

Sorry, not from 101. It should be from 103. Assume if I enter indicator as 'CR' and Function short name as 'PSM' in row 103, the column B show result as 'CR-PSM-49' instead of CR-PSM-37 which is coming now. The last available is CR-PSM-48.

So what value to expect in 101 given that the sequence (to date) depends on the count of Codes in Column E?

[avk]

Change the formula. Try with this in "B2" & copy down.

Please Login or Register  to view this content.

[GemBox]

Hi AVK,
I have tried your formula.. The column B should propose the next sequential number which is not proposing now. Check the attached file.

Change the formula. Try with this in "B2" & copy down.

Please Login or Register  to view this content.

[JohnTopley]

The issue is that we need to find the last old sequence for each combination of "Indicator" and "Short Function name" e.g CR-PSM-48: given the list in C is "random", I cannot think of a search/match function to find these.

And having found them, the same formula will be needed for every sequence update. We cannot use the COUNTIF function as used in the old sequence.

Not a good idea to have "disturbed" the old sequence!

[GemBox]

Hi John & AVK,
Any headsup?

[JohnTopley]

Some limited progress ..

this formula finds the last (highest) value for a given "Indicator"/"Short Name"

=IF(D101="","",INDEX(C2:C100,MAX(IF((LEFT(C2:C100,6)=D101&"-"&E101)*(ROW(C2:C100)),ROW(C2:C100)-1))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

It then has to modified to find the digits at the end and then add 1 so it is going to be a "nightmare".

A possible solution is the have a table which has the last value for a given combination and we use a "lookup" to get the next value.

See attached: table in H1:K7

[avk]

Let me confirm 103 : Is it sheet row number or your "SN" number.

[JohnTopley]

103 is "SN" number in row 101

[avk]

Yes Dear John agree (for your Post #8 attachment file).
"Gembox" statement : He say Automatci sequence number with condition, but my point is why break the link from SN 103 any logic behind. Because original ID already created. I think there is no need to create new ID.

In addition if we go as per "Gembox" : plz Look into column "C10" & "C11" (ID CR-PSM-3 & CR-PSM-5 respectively). If, in future, ID genrated without continuty i.e. CR-PSM-4 then, as per "Gemox" calculation new id is CR-PSM-37. Logically what is co-relation between original ID : CR-PSM-4 & new ID : CR-PSM-37. What is advantage.

[GemBox]

Hi AVK,
The entries in column 'C' are created manually and being assigned and used for tracking purpose. In order to automate, I have inserted a new column 'B' and starting from SN 103 the formula should check the last serial number from the existing entries in column 'C' and should generate with next available serial number without disturbing the old ones.

Yes Dear John agree (for your Post #8 attachment file).
"Gembox" statement : He say Automatci sequence number with condition, but my point is why break the link from SN 103 any logic behind. Because original ID already created. I think there is no need to create new ID.

In addition if we go as per "Gembox" : plz Look into column "C10" & "C11" (ID CR-PSM-3 & CR-PSM-5 respectively). If, in future, ID genrated without continuty i.e. CR-PSM-4 then, as per "Gemox" calculation new id is CR-PSM-37. Logically what is co-relation between original ID : CR-PSM-4 & new ID : CR-PSM-37. What is advantage.

[JohnTopley]

Now confused: in column B of "Sample8" file the last CR-PSM is "CR-PSM-56" so if we cannot start at "CR-PSM-48" .

[GemBox]

John,
Ignore the values in Column 'B' from SN 1 to 102 (we can make it blank). Start fresh from SN 103.

Now confused: in column B of "Sample8" file the last CR-PSM is "CR-PSM-56" so if we cannot start at "CR-PSM-48" .

[JohnTopley]

The only solution I have devised is to have a table with the LAST value for combination and use a combination of VLOOKUP/COUNTIF to derive the sequence number.

in C101

=IF($D101="","",($D101&"-"&$E101&"-"&VLOOKUP($D101&"-"&$E101,$J$2:$L$100,3,0)+1))

[GemBox]

Thanks much for your effort John, you have spent lot of time in this thread. I'll simulate your formula in my main sheet and let you know the result...

[JohnTopley]

Appreciate your feedback: thank you. I'll await the results!

[GemBox]

Thanks much John.. It works perfectly.. Sorry for my delayed reply, I was out of station...

Appreciate your feedback: thank you. I'll await the results!

[JohnTopley]

No problem: glad to read that all is OK.