Solve Using VBA Routine

[Jim May]

I have a table: A1:C11 as follows (Cells C2:C11 are Blank), but Macro
should fill in answer (given to the right):

Name Amt Order Answer to Order (=Rank ascending by Name)
Jon 13 ? 1
Jon 23 ? 3
Jon 15 ? 2
Peter 27 ? 2
Peter 10 ? 1
Alex 23 ? 2
Alex 34 ? 3
Alex 18 ? 1
Alex 36 ? 4
Mary 15 ? 1

The VBA routine should fill in C2:C11 with the above #'s
Can someone help me along?
TIA,

 

[Jim Cone]

Jim,

Determine the range
Find or insert a blank column adjacent to the range.
AutoFill the blank column with sequential numbers.
Sort the range (by Name then Amt). Include the sequential number column.
Loop thru the names, incrementing a counter in Column C.
When the name changes, start the counter over again.
Re-sort the range using the column with the autofilled numbers as the index.

Regards,
Jim Cone
San Francisco, USA

 

[Jim May]

Thanks Jim;
I'll give it a go -- in the AM..
Jim

Jim,

Determine the range
Find or insert a blank column adjacent to the range.
AutoFill the blank column with sequential numbers.
Sort the range (by Name then Amt). Include the sequential number column.
Loop thru the names, incrementing a counter in Column C.
When the name changes, start the counter over again.
Re-sort the range using the column with the autofilled numbers as the index.

Regards,
Jim Cone
San Francisco, USA

 

[Jim May]

Jim:
I got it !!! << This is my first (to me,,) real programming exercise.>>
<<vbg>>
I'm really excited. I'd never got it without your breaking the project
down into it's pieces - which I can't seem to do (my biggest problem, I
think..)
Again thanks,
Here is my go at it (probably could be improved on) - Do you see any obvious
improvement(s) that could or should be made?
Jim May

Sub Foo()
Range("D1").Value = "AutoNo"
ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select
Mrows = Selection.Rows.Count
For i = 2 To Mrows
Cells(i, 4).Value = i
Next i
ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Key2:=Range("B1"), _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("C2:C" & Mrows).Select
p = 0
For n = 2 To Mrows
If Cells(n, 1).Value = Cells(n + 1, 1) Then
p = p + 1
Cells(n, 3).Value = p
Else
Cells(n, 3).Value = p + 1
p = 0
End If
Next n
Range("A1").Select
ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("D").Delete
Range("A1").Select
End Sub

 

[Jim Cone]

Jim,

Only two things...
The variables have not been declared and
most of the "Select" statements are not needed.

I particularly like the way you incremented the Column C values.
For what it's worth, do not assume a "UsedRange" will always
start in row one. Of course, in this case it does.
'Slightly revised code follows...
'------------------------------------
Sub Foo()
Dim Mrows As Long
Dim i As Long
Dim n As Long
Dim p As Long

Range("D1").Value = "AutoNo"
' ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select
' Mrows = Selection.Rows.Count
Mrows = ActiveSheet.UsedRange.Rows.Count '*** Added

For i = 2 To Mrows
Cells(i, 4).Value = i
Next i

ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Key2:=Range("B1"), _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
' Range("C2:C" & Mrows).Select
p = 0

For n = 2 To Mrows
If Cells(n, 1).Value = Cells(n + 1, 1) Then
p = p + 1
Cells(n, 3).Value = p
Else
Cells(n, 3).Value = p + 1
p = 0
End If
Next n

' Range("A1").Select
ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("D").Delete
Range("A1").Select
End Sub
'---------------------------------------

Regards,
Jim Cone
San Francisco, USA

Jim:
I got it !!! << This is my first (to me,,) real programming exercise.>>
<<vbg>>
I'm really excited. I'd never got it without your breaking the project
down into it's pieces - which I can't seem to do (my biggest problem, I
think..) Again thanks,
Here is my go at it (probably could be improved on) - Do you see any obvious
improvement(s) that could or should be made?
Jim May

-snip-

 

[Jim May]

Thanks Jim for the additional comments;
appreciate your help on this.

PS:
I later came across a formula approach (as follows):
In Cell C2 (and copy down)..

{=COUNT(IF(($A$2:$A$11=A2)*($B$2:$B$11<=B2),$B$2:$B$11))}

enter as CSE type.

Jim May

 

[Jim Cone]

Jim,

You are welcome and thanks for passing the formula along.
You may find, like me, that once you start programming,
it seems to be easier to write code rather than figure out formulas.

For what it's worth, replace the second occurrence of "Jon"
with "Alex", and see what you get using the array formula.

Regards,
Jim Cone