Counting in groups or bunches

[BMR]

Hey guys,

I am trying to create unique SKUs for a data set I have. What I would like to accomplish is to create a new SKU for every new product. These SKUs should be numerical but I cannot use the drag tool or a basic formula because each product takes up anywhere between 2-50 rows. Each row may depict a different color/size but it is still the same product and needs the same SKU.

Example
E1-E5: "Writing Desk"
E6-E12: "Bookcase"
E13-E50: "Barstool"

Now what I want would be...
D1-D5: "SKU10001"
D6-D12: "SKU10002"
D13-D50: "SKU10003" and so on...

I tried coming up with a COUNTIF formula but to no avail. Ideas I have about how to do it would be If E2=E1, don't count up. If E2≠E1, count. That way it checks the cell above. If the cell above is the same it doesn't count up, if the cell above is different (meaning a change in product), the SKU will count up by 1.

 

[BMR]

I had my question answered. If anyone else has the same issue, here's the solution.

in d1 put this, without quotations - "sku10001"

in d2 put this, WITH quotations - ="sku"&IF(E2=E1,RIGHT(D1,5),RIGHT(D1,5)+1) drag down

 

[texasalynn]

or try this

Excel Workbook
	E	F
1	Writing Desk	SKU100001
2	Writing Desk	SKU100001
3	Writing Desk	SKU100001
4	Writing Desk	SKU100001
5	Writing Desk	SKU100001
6	Bookcase	SKU100002
7	Bookcase	SKU100002
8	Bookcase	SKU100002
9	Bookcase	SKU100002
10	Bookcase	SKU100002
11	Bookcase	SKU100002
12	Bookcase	SKU100002
13	Barstool	SKU100003
14	Barstool	SKU100003
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F1	="SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E1:$E$1,E1:$E$1)),"100000")
F2	=IF(E2=E1,F1,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E2,E$1:$E2)),"100000"))
F3	=IF(E3=E2,F2,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E3,E$1:$E3)),"100000"))
F4	=IF(E4=E3,F3,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E4,E$1:$E4)),"100000"))
F5	=IF(E5=E4,F4,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E5,E$1:$E5)),"100000"))
F6	=IF(E6=E5,F5,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E6,E$1:$E6)),"100000"))
F7	=IF(E7=E6,F6,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E7,E$1:$E7)),"100000"))
F8	=IF(E8=E7,F7,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E8,E$1:$E8)),"100000"))
F9	=IF(E9=E8,F8,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E9,E$1:$E9)),"100000"))
F10	=IF(E10=E9,F9,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E10,E$1:$E10)),"100000"))
F11	=IF(E11=E10,F10,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E11,E$1:$E11)),"100000"))
F12	=IF(E12=E11,F11,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E12,E$1:$E12)),"100000"))
F13	=IF(E13=E12,F12,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E13,E$1:$E13)),"100000"))
F14	=IF(E14=E13,F13,"SKU"&TEXT(SUMPRODUCT(1/COUNTIF(E$1:$E14,E$1:$E14)),"100000"))