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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Serial Number[/TD]
[TD]Batch[/TD]
[TD]New Serial Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]010869GN9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]053332AR3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]785870XE1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]46625HJT834[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]010869GN9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]76886PES8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]785870XE1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]053332AR3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
Serial Number	Batch	New Serial Number
010869GN9
053332AR3
785870XE1
46625HJT834
010869GN9
76886PES8
785870XE1
053332AR3

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

</tbody>

Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(COUNTIF($B$2:B3,B3)<>1,"",LOOKUP(2,1/($C$2:C2<>""),$C$2:C2)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks.