sequential number based another cell
 

A.............B................
1..Inv.......Lo
2..123......444
3.............456
4.............567
5..456......127
6.............541
7.............851
8.............999

I get a pivot table report like the above

I convert it to a static table and I want to add a Line# in Col.
like shown in cell C2. It will just be a sequential number like 1, 2
3, etc and will based off of the entry in Col A. When the number i
Col A changes I want the numbers in Col. C to start over again, 1, 2
3 etc. as in the table below

A B
1..Inv......Log........Line
2..123.....4444.......00
3............4567.......00
4............5678.......00
5..456.....1275.......00
6............5414.......00
7............8513.......00
8............9999.......00

Thanks for any help

Roge
--------
Message sent via www.excelforums.com

sequential number based another cell
 

Sub CCC()
Dim rng As Range, rng1 As Range, i As Long
Dim ar As Range
Set rng = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
Set rng1 = rng.Offset(0, -1).SpecialCells(xlBlanks)
For Each ar In rng1.Areas
ar(0, 3).Value = "'" & "001"
i = 1
For Each cell In ar
i = i + 1
cell.Offset(0, 2).Value = "'" & Right("000" & i, 3)
Next cell
Next ar
Set rng1 = Nothing
On Error Resume Next
Set rng1 = rng.Offset(0, 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Value = "'001"
End If
End Sub

--
Regards,
Tom Ogilvy



"martinrrrr - ExcelForums.com"
wrote in message ...
A.............B................C
1..Inv.......Log
2..123......4444
3.............4567
4.............5678
5..456......1275
6.............5414
7.............8513
8.............9999

I get a pivot table report like the above.

I convert it to a static table and I want to add a Line# in Col. C
like shown in cell C2. It will just be a sequential number like 1, 2,
3, etc and will based off of the entry in Col A. When the number in
Col A changes I want the numbers in Col. C to start over again, 1, 2,
3 etc. as in the table below.

A B C
1..Inv......Log........Line#
2..123.....4444.......001
3............4567.......002
4............5678.......003
5..456.....1275.......001
6............5414.......002
7............8513.......003
8............9999.......004


Thanks for any help.

Roger
---------
Message sent via www.excelforums.com

sequential number based another cell
 

It works great

Thanks again Tom
--------
Message sent via www.excelforums.com