VBA to Assign category numbers

[BORUCH]

hi all
i have an excel sheet that looks like below

I'm looking for a VBA that would automate for me column D and assign category numbers starting with 001 002 etc..

the criteria is if they are are all the same price, but also right underneath each other so for example the item number 00001 and item number c123 are both 100 dollars but not the same group

there will always be an empty row between each group

any help is greatly appreciated

thanks

 

[Peter_SSs]

Thanks for the XL2BB samples. One further issue to clarify please:
Is data like this possible and, if so, what would be the category results?

BORUCH.xlsm
	A	B	C	D
1	ITEM #	DESCRIPTION	PRICE	CATEGORY RESULTS
2	1	Chains	14.60
3	2	Socks	15.00
4	3	Bib-Shorts	15.55
5	4	Shorts	15.00
6	5	Tights	14.60
7	6	Other	14.60
8
Sample

 

[BORUCH]

Thanks for the XL2BB samples. One further issue to clarify please:
Is data like this possible and, if so, what would be the category results?

BORUCH.xlsm
	A	B	C	D
1	ITEM #	DESCRIPTION	PRICE	CATEGORY RESULTS
2	1	Chains	14.60
3	2	Socks	15.00
4	3	Bib-Shorts	15.55
5	4	Shorts	15.00
6	5	Tights	14.60
7	6	Other	14.60
8
Sample

Hi
It can be possible
The 14.60 would be 001 the 15 002 the 15.55 003

 

[BORUCH]

Any updates ?

 

[Peter_SSs]

I'm not really sure I understand the logic.
In post #10, row 5 was a single value in the group & was skipped.
In post #11, row 4 is a single value in the group but is not skipped.
Can you clarify the above and also advise the results for this group where there are 2 single values in the group but all other values are the same?

BORUCH.xlsm
	A	B	C
1	ITEM #	DESCRIPTION	PRICE
2	1	Chains	14.60
3	2	Socks	14.60
4	3	Bib-Shorts	13.00
5	4	Shorts	14.60
6	5	Tights	15.00
7	6	Other	14.60
Sample

 

[BORUCH]

I'm not really sure I understand the logic.
In post #10, row 5 was a single value in the group & was skipped.
In post #11, row 4 is a single value in the group but is not skipped.
Can you clarify the above and also advise the results for this group where there are 2 single values in the group but all other values are the same?

BORUCH.xlsm
	A	B	C
1	ITEM #	DESCRIPTION	PRICE
2	1	Chains	14.60
3	2	Socks	14.60
4	3	Bib-Shorts	13.00
5	4	Shorts	14.60
6	5	Tights	15.00
7	6	Other	14.60
Sample

I apologize the 15.55 would be skipped sorry for that

 

[Peter_SSs]

I apologize the 15.55 would be skipped sorry for that

Thanks.

.. and this?

.. and also advise the results for this group where there are 2 single values in the group but all other values are the same?

 

[BORUCH]

Thanks.

.. and this?

The 13 and the 15 in your example would be skipped

 

[Peter_SSs]

Try this with a copy of your workbook.
I have assumed that the values in column C are not the result of formulas.

Sub AllocateCategories()
  Dim rA As Range
  
  With Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "000"
    For Each rA In .Offset(, -1).SpecialCells(xlConstants).Areas
      rA.Offset(, 1).FormulaR1C1 = "=IF(COUNTIF(" & rA.Address(, , xlR1C1) & ",RC[-1])=1,"""",IFNA(VLOOKUP(RC[-1],R" & rA.Row - 1 & "C[-1]:R[-1]C,2,0),MAX(R1C:R[-1]C)+1))"
    Next rA
    .Value = .Value
  End With
End Sub

My sample data and results:

BORUCH.xlsm
	C	D
1	PRICE	CATEGORY RESULTS
2	14.60	001
3	14.60	001
4	13.00
5	14.60	001
6	15.00
7	14.60	001
8
9	100.20	002
10	496.00	003
11	496.00	003
12	100.20	002
13
14	25.00
15
16	90.00	004
17	100.00
18	90.00	004
19	90.00	004
20	90.00	004
21
22	20.00	005
23	20.00	005
24
Sample

 

[BORUCH]

Try this with a copy of your workbook.
I have assumed that the values in column C are not the result of formulas.

Sub AllocateCategories()
  Dim rA As Range
 
  With Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "000"
    For Each rA In .Offset(, -1).SpecialCells(xlConstants).Areas
      rA.Offset(, 1).FormulaR1C1 = "=IF(COUNTIF(" & rA.Address(, , xlR1C1) & ",RC[-1])=1,"""",IFNA(VLOOKUP(RC[-1],R" & rA.Row - 1 & "C[-1]:R[-1]C,2,0),MAX(R1C:R[-1]C)+1))"
    Next rA
    .Value = .Value
  End With
End Sub

My sample data and results:

BORUCH.xlsm
	C	D
1	PRICE	CATEGORY RESULTS
2	14.60	001
3	14.60	001
4	13.00
5	14.60	001
6	15.00
7	14.60	001
8
9	100.20	002
10	496.00	003
11	496.00	003
12	100.20	002
13
14	25.00
15
16	90.00	004
17	100.00
18	90.00	004
19	90.00	004
20	90.00	004
21
22	20.00	005
23	20.00	005
24
Sample

Hi
thanks for the code
one little problem is that if my data does not start from column C rather let’s say E13 or any other cell it would not work ,the same with column D it would be helpful if i would be able to choose which columns

 

[Peter_SSs]

Are you saying that the category results and the prices may not be in adjacent columns?

Would the rows always correspond? That is, if the 'Price' heading is in row 7 somewhere would the 'Category' heading also be in row 7 somewhere?

Need to understand just what sort of variation(s) you are contemplating. Please try to explain clearly, preferably with examples.