JeremyA1976
Board Regular
- Joined
- Aug 3, 2015
- Messages
- 59
I have 7 columns that contain data. In column A, there can be identical descriptions for each input. In column K, there is a total cost for that row entry. In column N, I have quantities (if applicable) for each entry. I have the sort vba code figured out as such...
Sub UnMerge4Sort()
'UNMERGES CELLS, SORTS BY COLUMN A & FORMAT PAINTS ROW 9 DOWN
Application.ScreenUpdating = False
Range("A10:P58").Select
ActiveWindow.SmallScroll Down:=-48
Selection.UnMerge
ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort.SortFields.Add Key:=Range( _
"A10:A58"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort
.SetRange Range("A10:P58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A9:P9").Select
Selection.Copy
Range("A10:P58").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have merged cells, so it simply unmerges, sorts based on column A in ascending order, then formats the unmerged cells back to normal based on row 9 format. What I am having trouble with is figuring out how to combine identical rows and sum their square footage (column G), pounds (column I), cost (Column K) & quantity (column N). The code would have to:
1.) figure out which rows are identical based on column A.
2.) Sum columns G,I,K & N.
3.) Place it in memory or in a hidden cell to recall and write.
4.) Delete the original identical rows information.
5.) Write in one row that would include the new total sums.
6.) Then search for the next identical
EXAMPLE:
[TABLE="width: 958"]
<colgroup><col span="11"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 6"]column A
Material Description[/TD]
[TD="colspan: 2"]column G
Sq./Ft. or Lin.Ft.[/TD]
[TD="colspan: 2"]column I
LBS.[/TD]
[TD="colspan: 2"]column K
Cost[/TD]
[TD]column M
ACT[/TD]
[TD]column N
QTY[/TD]
[/TR]
[TR]
[TD="colspan: 6"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]1" INSULATION 1 1/2 LB[/TD]
[TD="colspan: 2"]150[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]64.5[/TD]
[TD]12-60[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]1" INSULATION 1 1/2 LB[/TD]
[TD="colspan: 2"]550[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]236.5[/TD]
[TD]12-60[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2 1/2" STICK PINS[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]32.5[/TD]
[TD]12-60[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2 1/2" STICK PINS[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]13[/TD]
[TD]12-60[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2x2x3/16" ANGLE[/TD]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]73.2[/TD]
[TD="colspan: 2"]38.06[/TD]
[TD]12-40[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2x2x3/16" ANGLE[/TD]
[TD="colspan: 2"]150[/TD]
[TD="colspan: 2"]366[/TD]
[TD="colspan: 2"]190.32[/TD]
[TD]12-40[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
Essentially, I would be replacing multiple rows with one row which would have the totals (mentioned above). Is this even possible?? If someone could just help point me in the right direction, I would appreciate it.
Sub UnMerge4Sort()
'UNMERGES CELLS, SORTS BY COLUMN A & FORMAT PAINTS ROW 9 DOWN
Application.ScreenUpdating = False
Range("A10:P58").Select
ActiveWindow.SmallScroll Down:=-48
Selection.UnMerge
ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort.SortFields.Add Key:=Range( _
"A10:A58"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CHARGE OUT FORM").Sort
.SetRange Range("A10:P58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A9:P9").Select
Selection.Copy
Range("A10:P58").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have merged cells, so it simply unmerges, sorts based on column A in ascending order, then formats the unmerged cells back to normal based on row 9 format. What I am having trouble with is figuring out how to combine identical rows and sum their square footage (column G), pounds (column I), cost (Column K) & quantity (column N). The code would have to:
1.) figure out which rows are identical based on column A.
2.) Sum columns G,I,K & N.
3.) Place it in memory or in a hidden cell to recall and write.
4.) Delete the original identical rows information.
5.) Write in one row that would include the new total sums.
6.) Then search for the next identical
EXAMPLE:
[TABLE="width: 958"]
<colgroup><col span="11"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 6"]column A
Material Description[/TD]
[TD="colspan: 2"]column G
Sq./Ft. or Lin.Ft.[/TD]
[TD="colspan: 2"]column I
LBS.[/TD]
[TD="colspan: 2"]column K
Cost[/TD]
[TD]column M
ACT[/TD]
[TD]column N
QTY[/TD]
[/TR]
[TR]
[TD="colspan: 6"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]1" INSULATION 1 1/2 LB[/TD]
[TD="colspan: 2"]150[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]64.5[/TD]
[TD]12-60[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]1" INSULATION 1 1/2 LB[/TD]
[TD="colspan: 2"]550[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]236.5[/TD]
[TD]12-60[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2 1/2" STICK PINS[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]32.5[/TD]
[TD]12-60[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2 1/2" STICK PINS[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"]13[/TD]
[TD]12-60[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2x2x3/16" ANGLE[/TD]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]73.2[/TD]
[TD="colspan: 2"]38.06[/TD]
[TD]12-40[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="colspan: 6"]2x2x3/16" ANGLE[/TD]
[TD="colspan: 2"]150[/TD]
[TD="colspan: 2"]366[/TD]
[TD="colspan: 2"]190.32[/TD]
[TD]12-40[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
Essentially, I would be replacing multiple rows with one row which would have the totals (mentioned above). Is this even possible?? If someone could just help point me in the right direction, I would appreciate it.