Putting serial no. on column

[RahulNa]

Hi,

I am having an excel data where a serial number is required based on a particular column value.

Serial Number	Batch	New Serial Number
1	010869GN9	1
2	053332AR3	2
3	785870XE1	3
4	46625HJT834	4
5	010869GN9
6	76886PES8	5
7	785870XE1
8	053332AR3

<tbody>
</tbody>

I want when the first batch number comes there should be a serial number accordingly to it. Even if it get repeated many times there should be only 1 serial number on the first instance.

 

[ranman256]

place code into a module , then run BuildSerials

Sub BuildSerials()
Dim col As New Collection
Dim sBatch
Dim iSer As Long


On Error GoTo errCol


iSer = 1
Range("B2").Select
While ActiveCell.Value <> ""
   sBatch = ActiveCell.Value
   col.Add sBatch, sBatch
   ActiveCell.Offset(0, 1).Value = iSer
   iSer = iSer + 1
   
skipAdd:
   ActiveCell.Offset(1, 0).Select
Wend
Set col = Nothing
Exit Sub
errCol:
Resume skipAdd
End Sub

 

[sheetspread]

Excel 2010
	A	B	C
1	Serial Number	Batch	New Serial Number
2	1	010869GN9	1
3	2	053332AR3	2
4	3	785870XE1	3
5	4	46625HJT834	4
6	5	010869GN9
7	6	76886PES8	5
8	7	785870XE1
9	8	053332AR3
Sheet25

Cell Formulas
Range	Formula
C3	=IF(COUNTIF($B$2:B3,B3)<>1,"",LOOKUP(2,1/($C$2:C2<>""),$C$2:C2)+1)

 

[Gerald Higgins]

OR, with a formula, no VBA, assuming your first batch number is held in cell B2 . . .

In C2 . . .
=IF(COUNTIF(B$1:B2,B2)=1,MAX(C$1:C1)+1,"")

and copy all the way down.

 

[RahulNa]

Excel 2010

	A	B	C
1	Serial Number	Batch	New Serial Number
2	1	010869GN9	1
3	2	053332AR3	2
4	3	785870XE1	3
5	4	46625HJT834	4
6	5	010869GN9
7	6	76886PES8	5
8	7	785870XE1
9	8	053332AR3

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet25​

Worksheet Formulas Cell Formula C3 =IF(COUNTIF($B$2:B3,B3)<>1,"",LOOKUP(2,1/($C$2:C2<>""),$C$2:C2)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks.