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

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" <(E-Mail Removed)> wrote in message
news:7E157B5B-4132-4161-935B-(E-Mail Removed)...
> 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

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:7E157B5B-4132-4161-935B-(E-Mail Removed)...
> > 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
>
>

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


"NEWER USER" <(E-Mail Removed)> wrote in message
news:60F02779-EDBF-46B7-A113-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:7E157B5B-4132-4161-935B-(E-Mail Removed)...
>> > 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
>>
>>

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


"NEWER USER" wrote:

> 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

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> 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
>
>
> "NEWER USER" wrote:
>
> > 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

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
---------------
Jacob Skaria


"NEWER USER" wrote:

> 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

Thank you Jacob; you nailed it exactly.

"Jacob Skaria" wrote:

> 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
>
>
> "NEWER USER" wrote:
>
> > 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

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 (MVP - Excel)


"NEWER USER" <(E-Mail Removed)> wrote in message
news:7E157B5B-4132-4161-935B-(E-Mail Removed)...
>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

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

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:OFGngrA$(E-Mail Removed)...
> 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 (MVP - Excel)
>
>
> "NEWER USER" <(E-Mail Removed)> wrote in message
> news:7E157B5B-4132-4161-935B-(E-Mail Removed)...
>>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
>