Vba Code or Formula to Sum values in two specific columns based on unique value in another.

J

JMC57

Board Regular
Joined
Apr 24, 2012
Messages
118
I have values in Sheet 1 columns A-M. Column K contains the quantity purchased and column L contains Total Extended cost for a value (alpha numeric description) in column D. I am trying to sum the values in column K as well as values in column L and return based on the matching alpha numeric description in column D. I want the results in sheet 2 with. The data on all columns in a row are the same with the exception of K and L which I want to sum. I pasted some example data below since I cannot upload a file. Any help will be appreciated</SPAN>

[TABLE="width: 1501"]
<TBODY>[TR]
[TD]Division Name</SPAN>[/TD]
[TD]AHFS Therapeutic Code</SPAN>[/TD]
[TD]NDC Current Code</SPAN>[/TD]
[TD]RX Item Desc</SPAN>[/TD]
[TD]Generic Code Num</SPAN>[/TD]
[TD]Generic Seq Num</SPAN>[/TD]
[TD]Route Of Admin Code</SPAN>[/TD]
[TD]Dosage Form Desc</SPAN>[/TD]
[TD] Label Name</SPAN>[/TD]
[TD]Qty</SPAN>[/TD]
[TD]Total Extended Cost Amt - Sum</SPAN>[/TD]
[TD]Package Size</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00093030801</SPAN>[/TD]
[TD]CLEMASTIN FUM 100 2.68</SPAN>[/TD]
[TD]46691</SPAN>[/TD]
[TD]011656</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]TABLET </SPAN>[/TD]
[TD]CLEMASTINE FUMARATE </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]12.40</SPAN>[/TD]
[TD]100.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00093030801</SPAN>[/TD]
[TD]CLEMASTIN FUM 100 2.68</SPAN>[/TD]
[TD]46691</SPAN>[/TD]
[TD]011656</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]TABLET </SPAN>[/TD]
[TD]CLEMASTINE FUMARATE </SPAN>[/TD]
[TD]-1.00</SPAN>[/TD]
[TD]-8.06</SPAN>[/TD]
[TD]100.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00093030912</SPAN>[/TD]
[TD]CLEMASTIN FUM 4 OZ 0.67</SPAN>[/TD]
[TD]46990</SPAN>[/TD]
[TD]011654</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]SYRUP </SPAN>[/TD]
[TD]CLEMASTINE FUMARATE </SPAN>[/TD]
[TD]2.00</SPAN>[/TD]
[TD]24.96</SPAN>[/TD]
[TD]120.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00121048905</SPAN>[/TD]
[TD]DIPHENHYD 100X5 ML 12.5</SPAN>[/TD]
[TD]46032</SPAN>[/TD]
[TD]011592</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]ELIXIR </SPAN>[/TD]
[TD]DIPHENHYDRAMINE HCL </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]40.94</SPAN>[/TD]
[TD]5.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00121048905</SPAN>[/TD]
[TD]DIPHENHYD 100X5 ML 12.5</SPAN>[/TD]
[TD]46032</SPAN>[/TD]
[TD]011592</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]ELIXIR </SPAN>[/TD]
[TD]DIPHENHYDRAMINE HCL </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]40.94</SPAN>[/TD]
[TD]5.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00121048905</SPAN>[/TD]
[TD]DIPHENHYD 100X5 ML 12.5</SPAN>[/TD]
[TD]46032</SPAN>[/TD]
[TD]011592</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]ELIXIR </SPAN>[/TD]
[TD]DIPHENHYDRAMINE HCL </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]40.94</SPAN>[/TD]
[TD]5.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00121048905</SPAN>[/TD]
[TD]DIPHENHYD 100X5 ML 12.5</SPAN>[/TD]
[TD]46032</SPAN>[/TD]
[TD]011592</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]ELIXIR </SPAN>[/TD]
[TD]DIPHENHYDRAMINE HCL </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]40.94</SPAN>[/TD]
[TD]5.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00603082381</SPAN>[/TD]
[TD]Q-DRYL A/F 8 OZ 12.5</SPAN>[/TD]
[TD]48831</SPAN>[/TD]
[TD]016675</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]LIQUID </SPAN>[/TD]
[TD]Q-DRYL </SPAN>[/TD]
[TD]2.00</SPAN>[/TD]
[TD]2.94</SPAN>[/TD]
[TD]237.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00603082381</SPAN>[/TD]
[TD]Q-DRYL A/F 8 OZ 12.5</SPAN>[/TD]
[TD]48831</SPAN>[/TD]
[TD]016675</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]LIQUID </SPAN>[/TD]
[TD]Q-DRYL </SPAN>[/TD]
[TD]2.00</SPAN>[/TD]
[TD]2.94</SPAN>[/TD]
[TD]237.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00603082381</SPAN>[/TD]
[TD]Q-DRYL A/F 8 OZ 12.5</SPAN>[/TD]
[TD]48831</SPAN>[/TD]
[TD]016675</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]LIQUID </SPAN>[/TD]
[TD]Q-DRYL </SPAN>[/TD]
[TD]2.00</SPAN>[/TD]
[TD]2.94</SPAN>[/TD]
[TD]237.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00603082381</SPAN>[/TD]
[TD]Q-DRYL A/F 8 OZ 12.5</SPAN>[/TD]
[TD]48831</SPAN>[/TD]
[TD]016675</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]LIQUID </SPAN>[/TD]
[TD]Q-DRYL </SPAN>[/TD]
[TD]4.00</SPAN>[/TD]
[TD]5.88</SPAN>[/TD]
[TD]237.00</SPAN>[/TD]
[/TR]
[TR]
[TD]North Pole Division</SPAN>[/TD]
[TD]040404 - ETHANOLAMINE DERIVATIVES </SPAN>[/TD]
[TD]00603082381</SPAN>[/TD]
[TD]Q-DRYL A/F 8 OZ 12.5</SPAN>[/TD]
[TD]48831</SPAN>[/TD]
[TD]016675</SPAN>[/TD]
[TD]PO</SPAN>[/TD]
[TD]LIQUID </SPAN>[/TD]
[TD]Q-DRYL </SPAN>[/TD]
[TD]1.00</SPAN>[/TD]
[TD]1.47</SPAN>[/TD]
[TD]237.00</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL><COL span=8></COLGROUP>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
If I understand correctly, the resulting sheet will have 4 rows of data based on the four unique "NDC Current Code".

I see two choices. The first is a Pivot Table. It should be able to "sum" the two fields that you need and still provide all the other static data. The Second is VBA (Macro)
 
Upvote 0
Here is some code that may be useful.

It assumes that the source Data is sorted by column "D".
It will put the results in Sheet two starting in Row 1.

Code: 
Option Explicit


Sub Process()
    Dim ws As Worksheet
    Dim wsDst As Worksheet
    
    Dim SrcRowNo As Long
    Dim DstRowNo As Long
    
    Dim PrevD As String
    Set ws = ThisWorkbook.Worksheets(1)
    Set wsDst = ThisWorkbook.Worksheets(2)
    
    DstRowNo = 1
    Application.ScreenUpdating = False
    
    ws.Rows(1).Copy Destination:=wsDst.Rows(1)
    
    For SrcRowNo = 2 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        If Trim(ws.Cells(SrcRowNo, "D")) = PrevD Then
            wsDst.Cells(DstRowNo, "K") = wsDst.Cells(DstRowNo, "K") + ws.Cells(SrcRowNo, "K")
            wsDst.Cells(DstRowNo, "l") = wsDst.Cells(DstRowNo, "l") + ws.Cells(SrcRowNo, "l")
        Else
            DstRowNo = DstRowNo + 1
            ws.Rows(SrcRowNo).Copy Destination:=wsDst.Rows(DstRowNo)
            PrevD = Trim(ws.Cells(SrcRowNo, "D"))
        End If
        DoEvents
    Next SrcRowNo
    Application.ScreenUpdating = True
    
    MsgBox "Complete", vbInformation
End Sub
 
Upvote 0
Hi dear i want the same macro With little modification. I need number of rows for column I instead of sum. Can you please help me
 
Upvote 0
You did for sum but i need one column to sum and 2nd column to count for example i want to use column k for sum and and column i for count rows based on unique values in column D. Pleae help
 
Upvote 0
b.downey said:
Here is some code that may be useful.


It assumes that the source Data is sorted by column "D".
It will put the results in Sheet two starting in Row 1.

Code: 
Option Explicit


Sub Process()
    Dim ws As Worksheet
    Dim wsDst As Worksheet
    
    Dim SrcRowNo As Long
    Dim DstRowNo As Long
    
    Dim PrevD As String
    Set ws = ThisWorkbook.Worksheets(1)
    Set wsDst = ThisWorkbook.Worksheets(2)
    
    DstRowNo = 1
    Application.ScreenUpdating = False
    
    ws.Rows(1).Copy Destination:=wsDst.Rows(1)
    
    For SrcRowNo = 2 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        If Trim(ws.Cells(SrcRowNo, "D")) = PrevD Then
            wsDst.Cells(DstRowNo, "K") = wsDst.Cells(DstRowNo, "K") + ws.Cells(SrcRowNo, "K")
            wsDst.Cells(DstRowNo, "l") = wsDst.Cells(DstRowNo, "l") + ws.Cells(SrcRowNo, "l")
        Else
            DstRowNo = DstRowNo + 1
            ws.Rows(SrcRowNo).Copy Destination:=wsDst.Rows(DstRowNo)
            PrevD = Trim(ws.Cells(SrcRowNo, "D"))
        End If
        DoEvents
    Next SrcRowNo
    Application.ScreenUpdating = True
    
    MsgBox "Complete", vbInformation
End Sub
Click to expand...

Hi dear i want the same macro With little modification. I need number of rows for column I instead of sum. Can you please help me
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top