next available sequential number dependant on two additional variables

[Driver84]

Good day all,
I'm looking for some assistance in how to generate the next available sequential number dependant on two additional variables.

I am creating a document register and the two controlled variables are (the document owning) Group and Type.


The titles of the documents are to follow the format: GGGG_TTTT_NNNNN

Where:
GGGG, for example, is Group-A, Group-B, Group-C, etc.
TTTT is Type-A, Type-B, Type-C, etc.
and NNNNN is a numerical identifier using sequential integers of the format 00000, 00001, 00002, etc.

Group-A can have documents of Type-A, and Type-B both with the numerical identifier 00000, for example:

Group-A_Type-A_00000
Group-A_Type-B_00000


How can I ensure that if Group-A produce another Type-A document that the next available numerical identifier of 00001 is used, and allow another Type-B document to be produced that would also use 00001 as its next available numerical identifier?

MS Excel v2209 for Office 365.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[Driver84]

Hi Ali,
And thank you for the welcome.

Your advice is duly noted and I have edited my original post to include a sample workbook as advised.

[Glenn Kennedy]

One way:

=TEXT(COUNTIFS(B$3:B3,B3,C$3:C3,C3)-1,"00000")

[Glenn Kennedy]

or if you prefer:

=COUNTIFS(B$3:B3,B3,C$3:C3,C3)-1

and format as 00000

[Driver84]

Many thanks, Glenn, on both options. I greatly appreciate it!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Driver84]

Apologies for reopening this thread.

One additional condition that I did not consider in my original post (and would not have known about until I kindly received the working solution:
- How do I amend the formula =TEXT(COUNTIFS(B$3:B3,B3,C$3:C3,C3)-1,"00000") to show the cell as 'blank' if column B and/or C hasn't been set to one of the controlled values?
Attachment 801147
(Apologies, I can't seem to add additional attachments)

[Glenn Kennedy]

See file. Both options are present.

[Driver84]

Thanks for this, Glenn, I appreciate your help so far.
What I noted is that the formula is set up for only if Column B is 'blank':

=IF(B3="","",

If you delete a value from Column C the number defaults to -00001
Attachment 801281
I tried to amend this so that if Column B and/or Column C is 'blank', then there's no output:

=IF(B18="","",(OR(C18="","")),TEXT(COUNTIFS(B$3:B18,B18,C$3:C18,C18)-1,"00000"))

But when doing this I get the message "You've entered too many arguments for this function." Is there a way to overcome this?

[AliGW]

Try this:

=IF(OR(B18="",C18=""),"",TEXT(COUNTIFS(B$3:B18,B18,C$3:C18,C18)-1,"00000")