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

[JMC57]

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>

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

<TBODY>
</TBODY><COLGROUP><COL span=3><COL><COL span=8></COLGROUP>

 

[b.downey]

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)

 

[b.downey]

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.

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

 

[JMC57]

Works Perfectly! Thank you very much.

 

[b.downey]

Excellent... Thanks for the feedback

 

[engradnansw]

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

 

[engradnansw]

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

 

[engradnansw]

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.

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

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