AutoFill a large list

[NEWER USER]

I am very new to Excel and am trying to automate a process. I have a
worksheet with 4 columns and 60,000 rows.
Column A Group
Column B Part No
Column C Sales
Column D Rank

I would like to sort the worksheet on Column A (ascending) and then Column C
(descending). From here, I would like to Autofill Column D beginning with 1
to N and each time the Group changes, start again with 1 to N. Any help in
getting me started would be appreciated. Thanks

 

[Patrick Molloy]

in D use a formula
=ROW() - nn

if the first data row of your table is row 6 then replace nn by 5 , so the
formula returns 1

 

[NEWER USER]

I got the values to increment by 1 down the column, but how do I get it to
start over with 1 again as the Group value changes?

 

[Patrick Molloy]

if you use the ROW() function then row 3 will always have a 3 irrespective
of whether you sort or not - so long as the sheet calculates

 

[Jacob Skaria]

Assuming you have headers in Row1; enter the below formula in D2 and copy
that down as required..

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



If this post helps click Yes

 

[Jacob Skaria]

correction. In D2
=IF(A2=A1,D1+1,1)

If this post helps click Yes

 

[Jacob Skaria]

If you are looking for a macro..to attached to your automated process then
try the below which works on activesheet. Adjust to suit

Sub Numbering()
Dim lngRow As Long, lngLastRow As Long, lngNum As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
lngNum = IIf(Range("A" & lngRow) = _
Range("A" & lngRow - 1), lngNum + 1, 1)
Range("D" & lngRow) = lngNum
Next
End Sub



If this post helps click Yes

 

[NEWER USER]

Thank you Jacob; you nailed it exactly.

Assuming you have headers in Row1; enter the below formula in D2 and copy
that down as required..

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



If this post helps click Yes

 

[Rick Rothstein]

If a macro solution would be acceptable, here is one that should work nicely
for you. Simply put the starting number in a cell, then select from that
cell to the last cell you want to put your serial number in, and finally run
the macro.

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

Note: The macro changes the format of the selected cells to text (in order
to preserve trailing zeroes) and then right-aligns the text within the cell.

 

[Rick Rothstein]

Sorry.... I posted this to the wrong thread.