Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill a large list
Sorry.... I posted this to the wrong thread. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill list | Excel Discussion (Misc queries) | |||
Producing an automated list from a large list | Excel Worksheet Functions | |||
Autofill a list | Excel Worksheet Functions | |||
Using a list box to autofill | Excel Discussion (Misc queries) | |||
remove small list from large list | Excel Discussion (Misc queries) |