Unique Product ID - count if + rank???

[room99oat]

Hello,

I want to do Unique Product ID for my items and I have my data as shown in table below.

Column B is main category and Column C is sub-category, what formula should i use for excel to return unique ID as shown in column A.

ID Breakdown: M = main category, S = sub category, 01 = item number.

Thank you.

#	A (Unique ID)	B (Main Category)	C (Sub-Category)	D (Item Name)
1	M1-S1-01	Vegetable	Organic	AAA
2	M2-S1-01	Seafood	Shrimp	BBB
3	M2-S1-02	Seafood	Shrimp	BBB
4	M2-S2-01	Seafood	Fish	CCC-A
5	M2-S2-02	Seafood	Fish	CCC-B
6	M2-S2-03	Seafood	Fish	CCC-C
7	M3-S1-01	Meat	Pork	DDD-A
8	M3-S1-02	Meat	Pork	DDD-B
9	M3-S2-01	Meat	Chicken	EEE-A
10	M3-S2-02	Meat	Chicken	EEE-B

 

[cyrilbrd]

Hi, maybe something along:

excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	#	(Unique ID)	(Main Category)	(Sub-Category)	(Item Name)		Cat.	code		Cat.	code
2	1	M1-S1-01	Vegetable	Organic	AAA		Vegetable	M1		Organic	S1
3	2	M2-S1-01	Seafood	Shrimp	BBB		Seafood	M2		Shrimp	S1
4	3	M2-S1-02	Seafood	Shrimp	BBB		Meat	M3		Fish	S2
5	4	M2-S2-01	Seafood	Fish	CCC-A					Pork	S1
6	5	M2-S2-02	Seafood	Fish	CCC-B					Chicken	S2
7	6	M2-S2-03	Seafood	Fish	CCC-C
8	7	M3-S1-01	Meat	Pork	DDD-A
9	8	M3-S1-02	Meat	Pork	DDD-B
10	9	M3-S2-01	Meat	Chicken	EEE-A
11	10	M3-S2-02	Meat	Chicken	EEE-B
Sheet6

Cell Formulas
Range	Formula
B2:B11	B2	=TEXTJOIN("-",TRUE,XLOOKUP([@[(Main Category)]],MCat_table[Cat.],MCat_table[ code ],"not in DB",0),XLOOKUP([@[(Sub-Category)]],SCat_table[Cat.],SCat_table[ code ],"not in DB",0),TEXT(SUMPRODUCT(--(LEFT(E$2:E2,3)=LEFT(E2,3))),"00"))

 

[Peter_SSs]

what formula should i use

That will most likely depend on your version of Excel. Please update your Account details (found by clicking your user name at the top right of the forum) so that information is always available.

 

[room99oat]

cyrilbrd Thank you for you answer, but my 2019 version doesn't have xlookup. Are there any other ways?

Peter_SSs I use Excel 2019.

 

[Peter_SSs]

@Peter_SSs I use Excel 2019.

Thanks for updating your profile with this info.

Could you use something like this? Helper information in columns G, H, .. (or could be on another worksheet).
List Main categories across row 1. In my formulas I have allowed for up to 10 main categories (columns H:Q)
Formula in H2 copied across to Q2
From row 3 list any sub-category values that migh apply to that main category. In my formula I have allowed for up to 10 sub-categories per main category. Just alter the $12 in my formula if you want to allow for more.
Formula in B2 copied down.

A_MrExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	#	(Unique ID)	(Main Category)	(Sub-Category)	(Item Name)			Vegetable	Seafood	Meat
2	1	M1-S1-01	Vegetable	Organic	AAA			M1	M2	M3
3	2	M2-S1-01	Seafood	Shrimp	BBB			Organic	Shrimp	Pork
4	3	M2-S1-02	Seafood	Shrimp	BBB				Fish	Chicken
5	4	M2-S2-01	Seafood	Fish	CCC-A
6	5	M2-S2-02	Seafood	Fish	CCC-B
7	6	M2-S2-03	Seafood	Fish	CCC-C
8	7	M3-S1-01	Meat	Pork	DDD-A
9	8	M3-S1-02	Meat	Pork	DDD-B
10	9	M3-S2-01	Meat	Chicken	EEE-A
11	10	M3-S2-02	Meat	Chicken	EEE-B
Sheet2 (2)

Cell Formulas
Range	Formula
H2:Q2	H2	=IF(H1="","","M"&COUNTA($H1:H1))
B2:B11	B2	=HLOOKUP(C2,H$1:Q$2,2,0)&"-S"&MATCH(D2,INDEX(H$3:Q$12,0,MATCH(C2,H$1:Q$1,0)),0)&TEXT(COUNTIFS(C$2:C2,C2,D$2:D2,D2),"-00")

 

[cyrilbrd]

Or keeping the table system I suggested:
replace the formula using XLOOKUP with INDEX MATCH

 

[cyrilbrd]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	#	(Unique ID)	(Main Category)	(Sub-Category)	(Item Name)		Cat.	name		Cat.	name
2	1	M1-S1-01	Vegetable	Organic	AAA		Vegetable	M1		Organic	S1
3	2	M2-S1-01	Seafood	Shrimp	BBB		Seafood	M2		Shrimp	S1
4	3	M2-S1-02	Seafood	Shrimp	BBB		Meat	M3		Fish	S2
5	4	M2-S2-01	Seafood	Fish	CCC-A					Pork	S1
6	5	M2-S2-02	Seafood	Fish	CCC-B					Chicken	S2
7	6	M2-S2-03	Seafood	Fish	CCC-C
8	7	M3-S1-01	Meat	Pork	DDD-A
9	8	M3-S1-02	Meat	Pork	DDD-B
10	9	M3-S2-01	Meat	Chicken	EEE-A
11	10	M3-S2-02	Meat	Chicken	EEE-B
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=TEXTJOIN("-",TRUE,INDEX(MCat_table[name],MATCH([@[(Main Category)]],MCat_table[Cat.],0)),INDEX(SCat_table[name],MATCH([@[(Sub-Category)]],SCat_table[Cat.],0)),TEXT(SUMPRODUCT(--(LEFT(E$2:E2,3)=LEFT(E2,3))),"00"))