Incremented alphanumeric code for multiple entries

[JTGF]

Hello everyone,

I'm no excel pro so forgive me in advance for the lack of proper lingo. I'll try my best to explain everything correctly.

My goals is to create an interactive excel that will allow me to register multiple entrance and exits of material from my warehouse. This is just the first part, but I can't seem to find a solution to this problem out there.

So here we go:

Column B will be selected directly from a drop down list (This part was easy and its done)
Column C will pick the column B line (2 code letter from a set of 9 different codes) and generate a batch ID with "00n" or just "n" (see table bellow with example)

My issue is in generating column C output, I understand the logic, just can't make it happen in excel language:

I should be something like (ex.:Line 2):

1 - look in all C column for a number that starts with "B2" followed by n numbers - GF + 00n or n
2- from all the entries that start with "B2" give me the last entrie
3- return the next number in that alphanumeric code
4 - If nothing is found then write "B2" + 001 or 1

	A	B	C
1	Date (EU)	Material Code	Batch ID
2	04/07/2020​	GF	GF001
3	05/07/2020​	TD	TD001
4	06/07/2020​	JL	JL001
5	07/07/2020​	DM	DM001
6	08/07/2020​	PF	PF001
7	09/07/2020​	TQ	TQ001
8	10/07/2020​	PL	PL001
9	11/07/2020​	PS	PS001
10	12/07/2020​	PD	PD001
11	03/08/2020​	GF	GF002
12	03/08/2020​	GF	GF003
13	03/08/2020​	GF	GF004
14	03/08/2020​	DM	DM002
15	03/08/2020​	DM	DM003

Does the logic makes sense? Or is there anything here I'm missing?

Thank you in advance for your help, and hope you're all have a nice day.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C
1	Date (EU)	Material Code	Batch ID
2	07/04/2020	GF	GF001
3	07/05/2020	TD	TD001
4	07/06/2020	JL	JL001
5	07/07/2020	DM	DM001
6	07/08/2020	PF	PF001
7	07/09/2020	TQ	TQ001
8	07/10/2020	PL	PL001
9	07/11/2020	PS	PS001
10	07/12/2020	PD	PD001
11	08/03/2020	GF	GF002
12	08/03/2020	GF	GF003
13	08/03/2020	GF	GF004
14	08/03/2020	DM	DM002
15	08/03/2020	DM	DM003
Main

Cell Formulas
Range	Formula
C2:C15	C2	=B2&TEXT(COUNTIFS(C$1:C1,B2&"*")+1,"000")

 

[JTGF]

It worked perfectly, Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JTGF]

Hi @Fluff,

Recently I have made some modifications to the table above trying to use drop down tables and was asked to move it to google sheet. But I'm having an issue with the table.

The dropdowns work fine but when it come to the formula it works for the first line, But when I add new lines it goes off.

Sample description (manual input)	Sample type (drop down)	Sample ID (ProcV from sheet 2)	Sample ID number (formula)	How it looks in Googlesheets
bananas IND	bananas	BA	BA001	BA002
apples URG	carrots	CA	CA001	CA001
oranges BRZ	bananas	BA	BA002	BA002
Carrots US	bananas	BA	BA003
Apples US	apples	AP	AP001
CARROTS FR	bananas	BA	=C7&TEXT(COUNTIFS(D$1:D7,C7&"*")+1,"000")

The sample ID changes automatically as soon as you add the second row information. The idea was for people to add info to the table and the Sample ID to be generated automatically and in sequence.

Can you let me know what might be the issue and how I can fix it?

Thank you for your help

 

[Fluff]

I don't know anything about Sheets, so you would be better off starting a new thread in the General Discussion & Other Applications section & clearly state it's for Sheets.