Generate name based on cell data

[jesskidn]

Hi i need a formula for column G "job number" to generate based on the information in cells A-D. Would be great if it would populate as you type the data, separated by underscores and the number based on the division code increases.

for example, in the below, the job number would be "Self_NYM_Newsletter_001"
if there was another NYM code, the number would increase to "Self_NYM_Newsletter_002"
and if there is another division code, the number would restart to 001.

let me know if this makes sense, or if you need more information to help with this formula(s).

 

[Legacy 143009]

It is a perfect oppurtunity to use countifs.
G2:

=A2&"_"&C2&"_"&D2&"_"&COUNTIFS($A2:A2,$C$2:C2,$D$2:D2)

 

[Peter_SSs]

It is a perfect oppurtunity to use countifs.

.. but I don't think that you tested it?

@jesskidn
Welcome to the MrExcel board!
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have TEXTJOIN you could try col G otherwise the formula that I think @Flashbond meant to post (col H)

23 02 28.xlsm
	A		C	D			G	H
1
2	Self		NYM	Newsletter			Self_NYM_Newsletter_1	Self_NYM_Newsletter_1
3	Self		NYM	Newsletter			Self_NYM_Newsletter_2	Self_NYM_Newsletter_2
4	Self		ABC	Flyer			Self_ABC_Flyer_1	Self_ABC_Flyer_1
5	Self		NYM	Flyer			Self_NYM_Flyer_1	Self_NYM_Flyer_1
6	Self		ABC	Flyer			Self_ABC_Flyer_2	Self_ABC_Flyer_2
Textjoin

Cell Formulas
Range	Formula
G2:G6	G2	=TEXTJOIN("_",,A2,C2,D2,COUNTIFS(A$2:A2,A2,C$2:C2,C2,D$2:D2,D2))
H2:H6	H2	=A2&"_"&C2&"_"&D2&"_"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,D$2:D2,D2)

 

[Legacy 143009]

.. but I don't think that you tested it?

@jesskidn
Welcome to the MrExcel board!
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have TEXTJOIN you could try col G otherwise the formula that I think @Flashbond meant to post (col H)

23 02 28.xlsm
	A		C	D			G	H
1
2	Self		NYM	Newsletter			Self_NYM_Newsletter_1	Self_NYM_Newsletter_1
3	Self		NYM	Newsletter			Self_NYM_Newsletter_2	Self_NYM_Newsletter_2
4	Self		ABC	Flyer			Self_ABC_Flyer_1	Self_ABC_Flyer_1
5	Self		NYM	Flyer			Self_NYM_Flyer_1	Self_NYM_Flyer_1
6	Self		ABC	Flyer			Self_ABC_Flyer_2	Self_ABC_Flyer_2
Textjoin

Cell Formulas
Range	Formula
G2:G6	G2	=TEXTJOIN("_",,A2,C2,D2,COUNTIFS(A$2:A2,A2,C$2:C2,C2,D$2:D2,D2))
H2:H6	H2	=A2&"_"&C2&"_"&D2&"_"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,D$2:D2,D2)

I forgot the criterias, didn't I Sorry

 

[jesskidn]

Flashbond and Peter_SSs – Thank you VERY much. Both of these solutions are extremely helpful.

NOTE: I thought i posted an update that i was using google sheets, but it looked like i didnt hit post. Either way, i updated the about me section of my profile, as google sheets isnt an option to choose in the pre-determined options.

 

[Peter_SSs]

Since this now appears to be a Google Sheets question, I have moved it out of the Excel Questions forum into the correct forum.
Note the information on the main forum index page and please ask any future Google Sheets questions in the appropriate forum.

Never-the-less, I'm glad the suggestions helped. Thanks for letting us know.

 

[jdellasala]

In case you really want/need those leading 0's:

Book1
	A	B	C	D
1	Client	Division Code	Description	Job Number
2	Self	NYM	Newsletter	Self_NYM_Newsletter_001
3	Self	NYM	Newsletter	Self_NYM_Newsletter_002
Sheet1

Cell Formulas
Range	Formula
D2	D2	=TEXTJOIN("_",TRUE,A2,B2,C2,TEXT(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2),"00#"))
D3	D3	=A3&"_"&B3&"_"&C3&"_"&TEXT(COUNTIFS(A$2:A3,A3,B$2:B3,B3,C$2:C3,C3),"00#")