formula problem

[Andrew]

If I have data laid out as follows

Col1 Col2 Col3 Col4
Name1 111 tyr uuu
Name1 222 tyr uuu
Name1 333 tyr uuu
Name2 111 tyr uuu
Name2 222 tyr uuu
Name3 111 tyr uuu
Name4 111 tyr uuu
Name4 222 tyr uuu

How can I use a formula in a new column to get the
following output

Col1 Col2 Col3 Col4 Col5
1 Name1 111 tyr uuu
1 Name1 222 tyr uuu
1 Name1 333 tyr uuu
2 Name2 111 tyr uuu
2 Name2 222 tyr uuu
3 Name3 111 tyr uuu
4 Name4 111 tyr uuu
4 Name4 222 tyr uuu

the name in the above column 2 will always be the same
they will always be in consecutive rows as I will sort
first.

I dont know if this is possible but if it is it will save
me a lot of time :->

Andrew

 

[GaryDK]

Hi Andrew,

Assuming your data starts in B2, and A1 is not a number, enter this in
A2 and copy it down.

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

Hope this works for you,

Gary

 

[Guest]

Hi Andrew,
I am not certain that this will accomplish what you need, but it appears
that you want to put a count in column A. This will number the names,
assuming the names are grouped and sorted.

Sub Macro1()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "No"
Range("A2").Select
ActiveCell.Value = 1
Ct = 1
Do Until ActiveCell.Offset(0, 1).Value = ""
No1 = ActiveCell.Offset(0, 1).Value
No2 = ActiveCell.Offset(1, 1).Value
NoMinus = ActiveCell.Offset(-1, 1).Value
If ActiveCell.Address = "$A$2" Then ActiveCell.Value = 1:
ActiveCell.Offset(1, 0).Select: GoTo Bottomloop
If No1 = No2 Then
If No1 = NoMinus Then
ActiveCell.Value = Ct
Else
ActiveCell.Value = Ct
End If
Else
If No1 = NoMinus Then
ActiveCell.Value = Ct
Ct = Ct + 1
Else
ActiveCell.Value = Ct
Ct = Ct + 1
End If
End If
ActiveCell.Offset(1, 0).Select
Bottomloop:
Loop
End Sub

Thanks,