Increment a Column

[dr223]

Hi,

I have the following spreadsheet, which has one column B and I want to add column C whereby if the fileds is the same in column B to have a unique value, if not go to the next field and auto increment the number...

The C column should be appearing as shown, any help please

B C

mango 1
mango 1
mango 1
banana 2
banana 2
banana 2
banana 2
banana 2
orange 3
grape 4
grape 4



Many thanks

 

[VoG]

One way:

Enter 1 in C1.

In C2 and copied down

=IF(B2=B1,C1,C1+1)

 

[dr223]

I have done that but the value 1 is displayed in all the fields in Column C it is not changing to 2 when mango changes to banana

Any help please!

 

[VoG]

It works for me

Excel Workbook
	B	C
1	mango	1
2	mango	1
3	mango	1
4	banana	2
5	banana	2
6	banana	2
7	banana	2
8	banana	2
9	orange	3
10	grape	4
11	grape	4
Sheet2

 

[jim may]

Essentially the same as Peter's suggestion, but paste this code into the Sheet Module, then
just Double-Click anywhere in Column B for it to Run...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column <> 2 Then
Exit Sub
Else
Lr = Range("B" & Rows.Count).End(xlUp).Row
Target.Offset(, 1).Select
End If
ActiveCell.EntireColumn.Insert (xlToRight)
Range("C1").Value = 1
Range("C2:C" & Lr).Formula = "=IF(B2<>B1,C1+1,C1+0)"
End Sub