Adding Category column based on Item and Product columns

[Himaja]

Hi,

Say, I have below table (originally 10000+ rows) where I need to assign category number based on Item, Product columns and also below conditions. Can someone please help me on this

Each product has multiple items and each item has multiple products

Conditions:
Rows with same product number should be under same category number. Cannot break product into multiple categories
Similar item rows are also to be under same category ie., partial Items ("Item" Column) in a category are as minimal as possible
Ideal number of rows in each category should be <=150

Item	Product	Category (To be filled)
Item 1	Product 1	Cat 1
Item 2	Product 1	Cat 1
Item 3	Product 1	Cat 1
Item 1	Product 2	Cat 1
Item 1	Product 3	Cat 1
Item 5	Product 1	Cat 1
Item 4	Product 4	Cat 2

 

[bebo021999]

Try:

sample zipcode file.xlsx
	A	B	C
1	tem	Product	Category (To be filled)
2	Item 1	Product 1	Cat 1
3	Item 2	Product 1	Cat 1
4	Item 3	Product 1	Cat 1
5	Item 1	Product 2	Cat 1
6	Item 1	Product 3	Cat 1
7	Item 5	Product 1	Cat 1
8	Item 4	Product 4	Cat 2
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIF($A$1:A1,A2)=0,IF(COUNTIF($B$1:B1,B2)=0,"Cat "&SUMPRODUCT(1/COUNTIF($C$1:C1,$C$1:C1)),VLOOKUP(B2,$B$1:$C1,2,0)),VLOOKUP(A2,$A$1:$C1,3,0))

 

[Himaja]

Try:

sample zipcode file.xlsx
	A	B	C
1	tem	Product	Category (To be filled)
2	Item 1	Product 1	Cat 1
3	Item 2	Product 1	Cat 1
4	Item 3	Product 1	Cat 1
5	Item 1	Product 2	Cat 1
6	Item 1	Product 3	Cat 1
7	Item 5	Product 1	Cat 1
8	Item 4	Product 4	Cat 2
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIF($A$1:A1,A2)=0,IF(COUNTIF($B$1:B1,B2)=0,"Cat "&SUMPRODUCT(1/COUNTIF($C$1:C1,$C$1:C1)),VLOOKUP(B2,$B$1:$C1,2,0)),VLOOKUP(A2,$A$1:$C1,3,0))

Thanks a lot for your solution. Kudos to you. You did solve my first 2 conditions but the last condition of number of rows in a category > 150. Also one other request. Can you please explain me the formula on how it works? This would really help me in understanding how you put together formulas nicely and will also help me for my learning. Thanks in Advance.

 

[Himaja]

Thanks a lot for your solution. Kudos to you. You did solve my first 2 conditions but the last condition of number of rows in a category > 150. Also one other request. Can you please explain me the formula on how it works? This would really help me in understanding how you put together formulas nicely and will also help me for my learning. Thanks in Advance.

Sorry bebo021999, the formula that you gave did not work for products. I'm getting multiple same Product rows part of different groups.